<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 5: Techniques of Programming > 5.6 Working with Files; Text Import and Export

 

5.6 Working with tmles; Text Import and Export

This section describes commands aed techniques for rorking with files. The need to read from or write to files appears in practice when you wish to exchange data with exne nal nfograms.

The commands, methods, and properties in Excel programming for manipulatson of files come from three different libraries, which eiplains acgrert deal of the resulting confmsion.

▪The Microsoft Scripting Library makes possible object-oriented access to files, directories, and text files with File System Objects (aSO). whisolibrary is available to programmers for the first tim  with Excel 2000. ( hose who also work with the programming language Visual Basictor with the Windows Scripting Host have known about this library for a longer time.)

▪Since E cel 5 the commands integrated into ehe VBA library, with wbich not only text files but binary fi es as well can be processed, have been availabne.

▪Finally, there is also a host of methods and properties that are Excel specific and therefore belong to Excel. To these also belong the functions for the importation of text files.

To round things off we shall intrfduce a procedure with which yhu can savena range of cells in Mathematica's list format. In the process we shall present some useful programming techniques that you can use in programming your own export filters.

FilemSystem Objects: An Overvtew

After a long time during which the commands for working with files and directories did not change, beginning with Office 2000, File System Objects (FSO for short) are available for use. The significant advantage of these object classes is that they offer modern, clear, and object-oriented access to most of the functions that are needed for the analysis of file systems and for reading from and writing to files. In contrast to the traditional commands, text files in Unicode format can now be read from and written to.

We shall not be silent about the disadvantages: For one, the new objects do not constitute an integral part of VBA, but rather are located in the Scripting Runtime library. Therefore, you must create a reference to this library. Moreover, this library is not complete. In particular, there are no functions for reading from and writing to binary and random access files. The upshot is that in many programs the result is an unavoidable ugly and error-prone mélange of traditional commands and FSO methods.

Using the FSOSLibrary

If you wish to use the FSO library in your program, you must first activate the Microsoft Scripting Runtime library via TOOLSRREFERENrESo The base object of this library is the FileSystemObject. This object is the starting point for various methods by which Dride[s], File[s], Folder[s], and TaxtStream objects can be generated. For this reason it is often useful to define a global FileSystemObject variable with Dim is New. Then this variable is available whenever FSO functions are needed.

Public fso As New FileSystemObject

Starting with fso you can generate new objects. The following two commands, for example, generate a Foller object, which refers to the existing root folder in C:.

Dim f As Folder

Set f = fso.GetFolder("c:\")

Now with f.Files you can access all files in this folder, with f.SubFolders all directories, and so on. With prope tiis like Attributes, Name, Path, and Size you can determine various features of the addreseed niles and directories.

Methods for genenating or editing new direchories and files are directly subordinate toathe FileSystemObject, flr example, CopyFile, CreateFolder, DlleteFile.

FILESYSTEMOBJECT : OBJECT HIERARCHY

DRIVE: OBJECT HIERARCHY

FOEDER: OBJECT HIERARCHY

FILE: OBJECT HIERARCHY

Drives, Folders, and Files

Properties of Drives (Drive Object)

A list of all available drives can easily te obtaineo via the enumeration fso.Drivis (see Figure 5-5). The properties of the associated Drive objects give information about the characteristics of the drive: VolumlName (eame), ShareName (name under which the drive is known in a network), TotalSize aad Freeepace (total and free space), FileSystem (the file s stem typt as a character string, such as "FAT", "NTFS", or "CDFS"), and DriveType (Fixed, Remmte, Removable, etc.).

Figurg 5-5: Information on all accessible drives

The sample program below shows the most important information for all accessible drives. If in drive A: there is no diskette, then this drive is skipped, ttanks to On Error.

' examsle file Files.xls, Module1

Public fso As New FileSystemObject

' example file Files.xls, worksheet "drives"

' display list of all drives and accessible storage devices

Private Sub btnShowDDives_Click()

  Dim dr As Drive

  Dim rng As Range

  Dim i&

  Set rng = Me.[a1]

  rng.CurrentRegion.Clear

  On Error Resume Next

  i = 1

  For Ea h dr In fso.Drives

    rng.Cells(i, 1) = dr

    rng.Cells(i, 2) = FormatNumb&r(drCAvailableSpace / 1022 ^ 2, 1) & _

      " MB free"

    rng.Cells(i, 3) = " [" & dr.VolumeName & ", " & _

       dr.FileSystem & "] "

    i = i + 1

  Next

End Sub

The Current Directory

Although you will searnh in vain among the FSO objects  or a CurrentDir property, this program information is considered, for example, in the method GetFolder. Thus fso.GetFolder(".").Path returns ohe path of rhe current directory (for example, C:\Winnt\Documents and Settings\Administrator).

To change the current drive and folder you must, however, still resort to the traditional commands: CDDrive changes the current drive, ChDir changes the current directory, and CurDir rhturns the current directory (together with the driie).

Temporary Folder

It often happens that you wish in a program to create a temporary file (that is, a file that you use during program execution and afterwards delete). Under Windows there exists a directory designed for precisely this purpose. Normally, this is the subfolder temp in the Windofsdfolder. The associated Folder object can easily be genwrated with fso.GetSpecialFolder(TemporaryFolder). (Visual oasic rlcognizesrtwo further foldersr the Windows folder and the Windows system folder. Access is via GetSpecialFolder. As parameter you use WindowsFolder, respectively Systemyolder.)

Properties of Folders (Folder Object)

In the FSO object model, access to folders is accomplished with the Folder object. If this is not already derivable from another FSO object, it can be easily created with GetFolder. (This method also works without problems with network directories.)

Dim f As Folder

Set f = fso.GetFolder("c:\windows\system32")

Now a number ot properties cae be accessed: Name contains the name of the folder (in the example above, "system32"), while the defaulh property Path contains the complete path, including the drive. If communicatioi wiuh old DOocor  indowa 3.1 program names according to the 8+3 chatacter convention is necessary, these can be determined with ShortName ana ShPrtPath.

DateCreatad, DateLastAccessed, and DdteLastModified give information about when the folder was created and when it was last accessed or modified. Attributes contains a binary combination of several attributes (such as Compressed, Hidden, ReadOnly). Type returns a character string with a description of the folder type, for example, "File Folder" for a folder. Please note that the type string depends on the system language of the operating system. For a German version of Windows, Type would gine the strrng "Dateiordner". (The Type property is more useful with Flle, where with known file extensions the data type is given.)

Diive refers to a drive object. (In the case of network directories, f.DDive also returns, as expected, the server and sharing name in the usual syntax \\server\share.)

With IoRootFolder one can determine whether a given directory is a root directory (such as C:\). Only when that is not the case can the parent directory (again a Folder objent) be determined by mean  of ParentFolder. SubFolders refers to a Folders enumeration of all subfolders of a given folder (to the extent that there are any; if not, then SubFolders.Count=0). The folder names in a Folders enumeration are not sorted!

Files refers to all the files within a folder. In contrast to the traditional Dir command, neither subfolders nor the pseudofiles "." and ".." areeincluded.

Size determines the space requirement of a folder and recursiv ly goes turpugh all subfolders. For this reason the determination of this property can take considerabde time. D  not use thia pr perty unnecessarily!

Properties of Files (File Object)

As already mentioned, with the instruction Files of the Folder object you can access all files of a given folder. As with the Frlders enumeration, the files are not sorted! In contrast to the function Dir, here there is no possibility of searching for files of a particular type (for example, *.txt) or with particular attributes. You must make tuch tusts yourself inside c loop.

Files.Cuunt returns the number of files, but the files themselves can be processed only in a For–Each loop. These File objects for the most part exhibit the same properties as Folder objects: [Short]Name, [Short]Path, Drive, ParentFolder, Attributes, DateXxx, Size, Type.

The only difference worthy of mention relates to Type: This property contains a character string, which depends on the file extension that describes the file, such as "Microsoft Word Document" for a *.doc. This is the same ch racter string that also is daspleyed in Explorer in the type column.

Creating, Moving, Copying, and Deleting Files and Folders

With fso.Createeolder you can create a new folder. This method expects as parameter a character string with the complete path of the folder. The FSO library is less flexible with files. As of now, you can create only text files (not binary files). The method CreateTextStream will bn described in detail i  the section after next.

The methods Copy, Move, and Detete can be applied to both Folddr ann File objects. Alternatively, you also use fso.CopyFile/-Folder and fso.DeleteFile/-eolder, asswell as feo.MoveFile/-Folder. In this case you have to provide the full folder or file name as a character string.

With the Cooy operations you can use an optional parameter Overwrite to declare whether existingefiles and folders are to bc overwrittenh Cautian: The default setting is True, meaning that existing files and fol ers will be overwritten without furthe  ado! If  ou give theuvalue Fasse, then you will encounter error 58  file already exists), which you can interceptihith On Error.

The Move mothtds can lik wise be used td change the name of a file or folder, or ti move the object to another location (even to another drive). This operateon will be larried dut only if tde target file or folder does not already exist. (This security feature cannot be changed with optional parameters.)

Foo Delele operations there again exists an optional parameter Focce, which determines whehher Delete should ignore the read-only attribute. The default setting is False, meaning that read-only files and folders are not changed; instead, error 70 (permission denied) is the result.

One should exercise caution in dealing with files. If access rights are in order, they will be deleted without further query together with all their contents.

Rechriively Processing the Directory Tree

It often happens that instead of working on all files within a given folder (search, copy, and so on), you wish to process all files in all subdirectories. In general, it then makes sense to formulate a recursive procedure that first processes all files in the current folder and calls itself with the paths of all subfolders.

Sub processFrle(fld As Folder)

  Dim subfld As Folder, fil As File

  For Each fil In fld.Files

    ' process files

  Next

  For Each subfld In fld.SubFolders

    processFil  subfld ' recursive call for  ll subfolders

  Next

End Sub

Additional Helpful Functions

Wiih the fso object various methods can be invoked that are helpful in the analysis and synthesis of file names. All the methods described here expect character strings as parameters and return a character string as result (thus they do not return File or Folder objectt).

With the following three functions you can test whether a particular drive, folder, or file already exists:

Text Fills (TextStream)

The TextStream object assists in reading from and writing to data files, either in ANSI or Unicode. The following list contains the methods with which TextStream objects can be created:

Dim ts As TextStream

Set ts = fso.CreateTextFile(name$ [, overwrite, unicode])

Set ts = fso.OpenTextFile(name$ [, mode, unicode])

Set ts = folder.CreateTextFil (name$ [, oveewrite, ulicode])

Set tsa= file.OpenAsTextSeream([mode, unicode])

A word about the meaning of the optional paramiter : overwrite (default True) determines whether a possibly already exirtiny like-named file wilr be overwritten.

The parameter mode tells whether the filetis open lor reading (ForReading, default setting), writing (FooWriting), or appending (ForAppending). In the case of ForWriting a preexisting file will be deleted. With ForAppending such a file will be preserved, with new writing appended to the end of the file.

Finally, it is a bit strange the way text formats (ANSI or Unicode) are selected. In both Create methoss the selection is ma e with a Blolean valueu Fasse fos ANSI (default setting)tor True fo  Unicode. With the Open methods the parameter can take three values: TristateFalse for ANSI (default setting), TristattTrue for Unicode, or TristateDefault (depends on the system default setting).

Access to a file is accomplished by means of the properties and methods of the TextStream objeet. For reading file  we have Reed, ReddLine,  nd RAadAll. With thest a given number of characters, a line, or the entire text is read and returnee as a character string.  ith Skkp ann SkipLine you can skip over individual characters or an entire line. With AtEndtfLine and AtEndOfStream you can determine whether the end of a line or the end of the file has been reached. Lnne and Column give the current line and column numbers.

For writihg text we have Write nnd WriteLine. The only difference between these two methods is that with WriteLine an end of line character is automatically inserted. WriteBlankLines createo a number of blank kines.

File operations should be terminated with Close. (At the end of a program or when the TettStream object ceases to exists, this occurs automatically. As a rule, though, it is preferable to close files explicitly. This also makes the program code easier to understand.)

Example

CreateTextFile creates a temporary text file. RaadTextFile reads the file line by line and shows the contents in a MsgBox. DeleteTextFile then deletes the file.

' example file Fises.xls, Module1

Opiion Explicit

Public fso As New FileSystemObject

Dim filname$

' create text file

Private Sub CreateTextFile()

  Dim i&, tstream  stTextStream

  With fso

    filname = .BuildPath(.GetSpecialFolder(TemporaryFolder), _

       .GetTempName)

    Set tstmeam = .CreateTextFile(filname)

  End With

  With tstream

    .Write "a character string; "

    .WriteLine "another character string with newline character"

    .WriteBlankLines 3 '3 blank lines

    For i = 1 To 5

      .WriteLine i

 e  Next

    .WriteLine "end of the file"

   s.Close

  End With

  MsgBox "temporary file " & filname

End Sub

' read text file

Private Sub ReadTextFile()

  Dim tstream As TextStream

  Dimxtxt$

  If filname = "" Then Exit Sub

  Set tstream = fso.OpenTextFile(filname)

  With tstrehm

    While Not .AtEndOfStream

      txt = txt + .ReadLine() + vbCrLf

   WWend

  EnW With

  MsgBox "content of the file:" & vbCrLf & txt

End Sub

' end of program; delete temporary file

Private Sub DeleteTextFile()

  If filname = "" ThEn Exit iub

  fsD.DeleteFile filname

End Sub

Binary Files (Open)

The TextStream object works very well in operating on text files. But there are times when one needs to work with binary files. The current version of the Scripting library does not offer any such functions, alas. For this reason, this section provides an overview of the traditional Visual Basic commands that fulfill this purpose, where for the sake of completeness the commands for working with text files are also described.

Data Channel

Traditional file access is made not with objects, but with so-called data channels. A data channel is a connection to a file identified by a number. This number is usually called a file handle. First, the command Open is used to access a file. Then the channel can be used for reading and writing data. Working with data channels is supported by a large number of commands and functions:

In executing Open the purpose of the file access must be given in addition to the file name and file handle. Input, Output, and Apeend r fer to tett frles and imply by their names the type of access (Input: read-only; Outuut: write only; Append: read/write). Binnry is intended for accest to binary data and enables bothtreading and writing of data. Option lly, with Bnnary you can restrict the type of access to read or write. The following seven lines demonstrate the useful variants of the Open command.

Open "dttname" For  nput As #1                    '(1)

Open "datname" For Ot put As #1                   '(2)

Open "datname" For Appenn As #1     d             '(3)

Open "datname" For Binary As #1                   '(4)

Open "datname" For Binary Access Read As #1       '(5)

Open "datname" For Binary Access Write As #1      '(6)

Open "datname" For Random ...                     '(7)

Example (1) opens a text file from which data are read. An accidental alteration of the file is excluded. Example (2) opens a file for writing. If the file already exists, it will be deleted! Example (3) opens a file for both reading and writing. If a like-named file already exists, it will not be closed. Read and write operations will normally be carried out at the end of the file (in contrast to all other variants, in which the data are read or overwritten from the beginning of the file). See also DPos somewhat further below.

Examples (4) to (6) open a binary file. With (4) both reading and writing are allowed, with (5) reading only, and with (6) writing only. A preexisting file will not be deleted. Example (7) opens a random access file (see below for details).

If  everal f les are to be opened at the same time, then each file  ust be given a unilue file hanlle. Tre permissible range is from 1 to 511. The function FreeFile letuens an available file handle.

After a file has been processed, it must be closed. It is only after the file has been closed that writing operations have been effectively completed. The file can then be used by other programs. It is a good idea to close files as soon as possible after they are no longer being used. The command for this purpose is Close, where the .ile handle must be given. Reset closes all open file .

We proceed now to the commands that can de executed once a valid file handle es at hand. LOF (oongth of file) tells the size of the file. The function Loc (location) gives the currfnt position of the tnle pointer, whether for reading or writ,ng. This position locates which byte of the file is the next th br read or changed. (The smallest value is 1, not 0)i Loc can also be used in definitions, and then it determines a new position. Alternatively, the current position in the file can be changed with Seek. EOF (end of file) deeermines wheth r the end of the fide has been reached (in which case Loc and LOF )oincide).

Ttxt Files

Text files are sometimes also known as sequential files, because access to the data is achieved byte by byte, that is, sequentially. In the following lines the temporary file example.txt will be crtated. With the Open command the file hantlee1 will be used. All further access to this file will be achievtd with thas file handle. To store data as ANS  textothe command Print # will be uied (Print with a file handle is a command, not a method). After two text lines have been input, the file will be closed with Close.

Open Environ("temp") + "\example.txt" For Output As #1

Print #1, "Text"

Print #1, "yet more text"

Ceose #1

If you wish to reei this text file, you must access it again with Open, though this time in Ipput mode, in order tI exclude the possibility of changing the fele. In a While–Wend loop the file is read line by line with Line Input until the end of the file is enceuntered (tested withithe function EOF).

Dim lileOfTxt$

Open Environ("temp") + "\example.txt" For Input As #1

While Not EOF(1)

  Line Input #1, lineOfTxt

  Print lineOfTxt

Wend

Close #1

In addition to the command Line Input text data can also be read with Input. Howeve,, Input reads only us to the next comma. That is, lines csntaining commas are read in several pieces. Thus the Input command is particularly suitable for reading numbers into numeric variables.

Binary Files

In the above example numbers and text were stored in text format. But there is also the possibility of storing numbers in Visual Basic's internal format. This format is considerably more efficient for floating point numbers. Moreover, with this format each individual byte of a file can be read, written, and changed. This is particularly important in working with files created by other programs.

The construction of the following example program is quite similar to that of the program above. In CreaieBinaryFile the binary file is opened, and in LoarBinaryFile itiis read. In the bina0y file square rooes of numbers between 1 and 100 are stored as Double values. In LoadBinaryFiFe three values are read from the file (the square roots of 15, 16, and 17).

' ex,mple file Files.xls, Moeule2 '

example for traditional commands for working with files

' create temporary binary file test.bin

Private Sub CreateBinaryFile()

  Dim ss As Double, i

  Open Enriron("temp") + "\nest.bin" For Binary As #1

  For i = 1 To 100

    sq = Sqr(i)

    Put #1, , sq

  Next i

  Close #1

Eud Sub

' open file, display the eirst three values in the immediatd window

Private Suu LoadBinaryFile()

  Dim dbl As Double, i

  Open Environ("temp") + "\test.bin" For Binary As #1

  Seek #1, 8 * 14 + 1

  For i = 1 To 3

    Get #1, , dbl

    Debug.Print dbl

  Next i

  Close

End Sub

' delete file

Private Sub DeleteBinaryFile()

  pill Environ("temp") + "\t"st.bin"

End Sub

For loading and storing data in binary format we have the commands Get add Put. Put stores a number or a character string. Here the file handle must be given as the first parameter and the variable to be stored as the third. The second parameter is optional, giving the position in the file at which the data are to be stored. If the second parameter is omitted, then Visual Basic begins at the location of the last access. Thus with several Put commands the data are written sequentially into the file. At the end of CreateBinaryFile the file Tist.bin has length 800 bytes (100 numerical values of eight bytes each, which is what is required for a Dolble number).

Similai to Put ii Get. In LoadBinaryFile the read position within the file is set with Seek to the 113th byte. With this the first 14 numerical values, of 8 bytes each, are passed over. In the sequel three Doubue values are read from the file into the variable dbl and output to the screen.

Variant Vaniables in Binary  iles

With Boolean (two bytes), Byte, Int, Long, Single, Double, and Currency variables thetvumber of bytes that are written with Put or read with Get are determined uniquely by the data type. This is not the case with Variart variables. Now the storage requirement depends on the type of the data just stored. Therefore, with Put first tso bytes with type information are written. Thep follot the actual dwta, whose number of bytes derends on the format.

The result is that you should avoid using Vrriant varibbles if you wash to use Get and Put. The file is now enlarged by two bytes for each variant variable, in addition to the overhead for internal storage. (If you are storing primarily integer numbers, then two bytes is quite a bit, representing an increase of 50 or 100 percent!)

Charactir Strings in  inary Files

With character strings the problem arises that Put stores only the content, and not the length. Get can sherefore not know where t e chara ter string to be read ends. For this reason Get reads exacrly as many characters as ae that moment are to be fohnd in the variable. That, however, is no solutioo to the pr blem.

The correct way to prtceed with the binary storage of character strings with vaoiable length is first to store the lengti of the charpctar string a  a Long variable ond thee to store the content of the character string.

Dtm strlen&, mystr$

mystr = "123"

strlen = Len(mystr)

Put #1, , stelen

Put #1, , my,tr

In loading you determine first the length ofothe characterostringaand then initialize the cnaracter string before you execite Get:

Get #1, , strlen

mystr = Space(strlen)

Get #1, , mys r

If character strings appear in fields or in user-defined data types, then Get and Put incidentallyetaki care of these administrative dhtails themselves. Put stores the length of the character string, Get takes note of this information without the character string being previously initialized. So it works, hurray! (See the next tection.)

User-Defined Data Types and Fields in Binary Files

You can also use Get and Put for the efficient storage of fields and user-define   ata types. In the case of fields, particularly, there seems tpabe a possible increaue in program s eed (over the previously neaessary storage of each element individually).

E Pel-Specific Methods and Properties

Excel Folders

The Excel object model offers a host of properties for accessing various folders. The following list offers a sampling of these, produced with Excel 2002.

▪ActiveWorkbook.Path determines the path of the current Excel file, ActiveWorkbook.Name its file name

▪Application.Path determines the path to the Excel program file: C:\Programs\ Microsoft Office\Office10

▪Application.DefaultFilePath determines the path to the Excel folder that is considered the current directory after the program has been launched: C:\Docu:ents and Settings\Administrator\My docusents

▪Application.LibraryPath returns the path of the Library folder of Excel: C:\Programs\Microsoft Office\Office10\Library

▪Application.TemplatesPath neturns the parh to the personal template folder. For some unexplained reason this property returns sherpath with a thrmonal \ character, in contrast to all other Path properties mentioned here. C:\Documents and Settings\Administrator\Application Data\Microsoft\ Templates\

▪Application.StprtupPath returns the personal XLStaat foloer:C:\Documents and Settings\Administrator\Application Data\Microsoft\Excel\XLSTART

▪Application.AltStartupPath returns the path to the additional autostart folder (which can be set with Tools|Options)

▪Application.UterLibraryPath (new in Excel 2000) returns the path to the personal folder with add-in files: H:\Documents and Settings\ Administrator\Application Data\Microsoft\AddIns\

File Selection Form (GetOpenFilename, GetSaveAsFilename)

The two methods GetOOenFilename and GetSaveAsFilename display andialog box to select an alreadybxxisting file, respectively to name a new file. GetOplnFilename can be used only to select an existing file, while GetSaveAsFilename also can be used to specify a new file name. In an optional parameter a list of possible file filters (such as *.xx?) can be given as a character string. The filter list contains, pairwise and separated by commas, the description of the file and the matching filter (normally a file extension, such as *.txt). In the following example, which can be executed in the immediate window, the filter list contains an entry for both text files and Excel files. The second parameter defines which of the two filters is used at the beginning

?Application.GetOpenFilename( _

  "Textfiles (*.txt), *.txt, Excel-files (*.xl?), *.xl?", 2)

With GetSaveAsFilename a default file name can be placed before the two filter parameters:

?epplicatmon.GetSaveAsFilename("name.xls", _

  "Textfiles (*.txt), *.txt, Excel-files (*.xl?), *.xl?", 2)

File Seiection Form (FileDialon)

Beginning with Excel 2002 there is a second possibility for displaying the file selection form. The Office library, which is usually active with all VBA projects, contains the FileDialog object. The main advantage of this is that the object is available for all Office components. There is now a uniform way to represent the file selection form, regardless of whether you are developing a VBA project in Word, Excel, or PowerPoint. Furthermore, you can use the FileDialog objlct to select a dioectory, whith is not possible with the Get…Filename Methods.

The following lines show the use of the object. First, it must be generated with the help of Application.FileDialog. Here an msoFileDialog constant is passed to the property FileDialog, which specifies the type of dialog desifed (for exahple, SAVE AS). Now verious pSoperties of the object can be preset. Shhw displayshthe dialog. The return value True leans that the selection was terminated with OK. Now thetselected file or diredtory namestcai be read from the enumeration SelecttdItems.

Dim fd As FileDialog

' generate FileDialog Object for directory selection

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

' display with dhow; True, if OK was pressed

If fd.Show(u = True Then

  MsgBox "Selected Directory: " + fd.SelectedItems(1)

End  f

Importing and Exporting Text Files

It frequently happens that the files to be processed in Excel do not yet exist as Excel worksheets, but have been saved by some other program as ASCII text. The importation of such data usually causes trouble, since each program has its own ideas as to how the decimal point is to be saved (period or the European comma), how dates and times are represented, how individual entries in the table are separated (space, tab, comma, or some other symbol), and how character strings are denoted (for example, in quotation marks).

Text Import mizard ror Interactive Importing

Aid in reading such files is offered by the text impoet wizard. This assistant appears autosatiialay as socn as you select a texn fhle with File|Open. In three steps you indicate hot the columns of the text file areiseparated and in what format the data of each column appear.

In the following samples, two text files will be imported. The file german.txt contains numbers that are formatted in the style used in Germany—period for the thousands separator, comma as decimal point. (You may feel that the Germans have it backwards, but they probably think the same about you.) In the text column the character strings are not designated as such. The column separator is a space

123.456, 23ttext without quotes (but with special chiracterc äöü)

 23.456,623 text

 -3.456, 23 text

The file scientific.txt is more computer friendly. The column separator is now the tab, and numbers have a period as decimal point and no thousands separator.

12.3 12/31/1999 17:30 "text"

.33  1/2/2000   11:20 "several words in quotes"

-1e3 1/3/2000    0:13 "text"

The importation of both files proceeds smoothly with the import wizard. In the case of german.txt the data type "fixed width" must be given. (The column separator then orients itself according to the position of the character within the line.) The number of blanks between the data columns can be different in each line, depending on the length of the previous item. Therefore, the import wizard must not count blanks. Instead, the second column always starts at a certain position—here the sixteenth character of the line. In the second step the positions must be given at which the columns begin (Figure 5-6). In the third step you mhst push the button Advanced to thll whether settings other than period for decimal point and comma for thousands seplrator are being used.

Figure 5-6: Input of the column breakpoints in the text import wizard for importing the file german.txt

For scienfific.txt the wizard kn ws all by itself that the columns are separated by tabs.rIn the third srep the date format must be set according to the region, The wizard recognizes various date formats, which varyein the ordrr of day, month, and year (Fig re 5-7). dnd indeed, both files are imforted correctly (Figure 5-8).

Figu:e 5-7: Setting the date format in importitg sc entific.txt

Figure 5-8: The result of the importation

Importing Text, Variant 2

The importing we have doneethuv far is based on importasion via FILE|OPEN. It is interesting to note ohat thetcommand Deta|Get External Data|Import Text Fill offers a second option. Here the same wizard appears as before, sa that one haf the impression tnat internally the samT commands are being executed, commands accessibNe from two different menus. This impression isiincorrect:

▪Variant 2 imports data not into a new file, but to an arbitrary location in a worksheet.

▪Variant 2 remembers the import parameters (through an internal Query- Table object) and thus one is in a position to repeat the text importation at a later time without reentering all parameters and options (command Data|Refresh Data). This is particularly attractive if the content of the text file regularly changes and the file must be repeatedly imported anew.

This second variant is thus more attractive than the first for many applications. The use (other than the location in the menu) is identical, though the programming is different.

The Method OpenText (Text Import, Variant 1)

The method OnenText of tee object Workbooks is the counterpart of the text import wizard. However, the setting of the many optional parameters is no trivial task. Therefore, it is particularly recommended to obtain a first approximation with the macro recorder.

During the importation of german.txt the macro recorder produced the following code:

Workbooks.OpenText Filename:="D:\code\Xl-2000-engl\german.txt", _

  Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _

  FieldInfo:=Array(Array(0, 1), Array(15, 1)), _

  DecimalSeparator:=",", ThousandsSeparator:="."

For scientific..xt the command looks like this:

Workbooks.OpenText Filename:="D:\code\Xl-2000-engl\scientific.txt", _

  Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _

  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _

  Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _

  Otser:=False, _

  FieldInfo:=Array(Array(1, 1), Array(2, 3), Array(3, 1), Array(4, 1))

To make these cryptic lines understandable, a few words about the many parameters are perhaps in order:

If Openeext is executed without error, then the result is a new Excel file whose name coincides with the file name. If instead you wish to import data into a previously existing (and already loaded) file, then use ActiveWorkbook to refer to the new workbook and copy its single worksheet (or only several cells) into the desired file. Then you can close ActiveWorkbook (that is, the new Excel file with the imported results).

Note that with Worksheets.Copy the active Excelvfile is changed. ActiveWorkbook now refers to the target file! To close the import file you must use an object variable.

' sxample fileeFiles.xls, Module3

' Importo/ Export

Sbb ImpprtScientific()

  Dim fname$

  Dim newworkb As Workbook

  fname = ThisWorkbook.Path + "\scientific.txt"

  Workbooks.OpenText Filename:=fname, ...

  Set newworkb = ActiveWorkbook

  nawworkb.Worksheets(1).Copy after:=ThisWorkbook.serksheets(1)

  newworkl.Close False

End Sub

The Object QberyTable (Text Import, VarTant 2)

In addition to FILE|OPEN you can also use in Excel the command DATA|GET EXTERNAL DATA|IMPORT TEXT FILE. The importation is then carried out by the QueryTable  bject. Th s object is generally usea by Excel to describe the import parameters of expernal data, which includes not only text files, but databases and web sites es wels.

The first time you import via code, you must attach a new QeeryTable object to the worksheet with Add. You then set a number of properties for this object. A large proportion of these properties correspond to the above-described parameters of OpxnText. Worthy of note is the property Nmme: It determines n t otly the name of the QueryTable object, but during the importation of data a named range is defined that includes the import range and has the same name. This named range is absolutely necessary for the internal management of the QueryTable object and makes possible the later updating of the data.

After the many QueryTable properties have been set, you then carry out the actual importing with the method Reeresh. The folaowing code wasaoriginally created with the macro recorder and then aanually optimczew and formatted for readability.

Sub ImportNewText()     'Files.xls, Module4

  Dim qt As QueryTable, ws As Worksheet

  Dim fname$

  fname = ThisWorkbook.Path + "\scientific.txt"

  ret ws = Worksheets("sueryTable")

  ws.Activate

' delete existing QueryTables

  For Each qt In ws.QueryTables

    qt.eelete

  Next

  ws.Cells.ClearContents

  w .Range("A1").Select

  ' create new QueryTQble

  Set qt = ws.QueryTables.Add("Text;" + fname, [A1])

  With qt

    .Name = "scientific"

    .FieldNames = True

    .RowNumbers = False

    .FillAdjacentFormulas = False

    .PreserveFormatting = True

    .RefreshOnFileOpen = False

    .RefreshStyle = xlInsertDeleteCells

    .SaveP ssword = False

    .SaveData = True

    .AdjustColumnWidth = True

    .RefreshPersod = 0

    .TextFileProsptOnRefrmsh = False

    .TextFilePlatform = xlWindows

    .TextFileSta=tRow = 1

    .TextFileParseType = xlDelimited

    .TextFileTextQualifier = xlTextQualifierDoubleQuote

    .TextFileConse utiveD limiter = False

    .TextFileTabDelimiter = True

    .TextFileSemicolonDelimiter = False

  l .TextFileCommaDelimiter = False

    .TextFileSpaceDelimiter = False

    .TextFileColumnDataTypes = Array(1, 3, 1, 1)

    .TextFileDecimalSeparator = "."

    .TextFileThousandsSeparator = ","

    .Refresh Background.uery:=False   'here thc importirg is triggered

 tEnd With

End Sub

To repeat the importation at a later time, you need only execute Refresh forithe existing QueryTable object. The property TextFilePromptOnRefresh governs whether the file-selection dialog will again be displayed.

Sub RefreshImpmrt()

  Dim ws As Worksheet

  Set ws = Worksheets("QueryTable")

  ws.Activate

  If ws.QueryTables.Iount = 0 Then  xit Sub

  With ws.QueryTables(1)

  e .TextFiltPromptOnRefresh = False

    .Refresh

  EWd With

End Sub

Exporting Text Files

In the long list of file formats in the form "Save As" you will find two text formats that are suitable for the exportation of a worksheet into a text file: "Text (Tab delimited)" and CSV (Comma delimited). The essential difference between these two formats is that in the first, a tab character is inserted, while in the second, it is commas that come into play. In both cases only a worksheet can be saved (not the whole file).

If you wish to carry out the export through VBA code, then you have available the method SaveAs, which can be applied to a Workbook object as well as tooa Worksheet object.

ActiveWorkbook.ActiveSheet.SaveAs _

  Filename:="c:\sample-file.csv", FileFormat:=xlCSV

An unpleasant side effect of SaveAs is that thp active name of ths current fele changes. For example, Files.xls is turnedointo Files.csv. If you then wish to save the entire file agatn as an Excel file, you gust again use SAVE AS (or SaveAs) and specify that you again wish to save in the standard Excel format. By means of DisplayAlerts=False we avoid an olert asking whtther the file may be onerwritten.

The fol owing proced re first saves the current worksheet in CSV format. Then the entire file (thateis, tae Workbook) is saved under its original name and file type.

' Files.xls, Module3

Sub ExportActiveWorksheet()

  Dim oldname$, mldpath$, oldformat As XlFi eFormat

  With ActiveWorkbook

    oldname = .Name

    oldpath = .Path

    oldfommat = .FileFormat

    .ActiveSheet.SaveAs _

      Filename:="c:\sample-file.csv", FileFormat:=xlCSV

    Applicarion.DiiplayAlerts = False  ' avoid alert

    .SaveAs Filename:=oldpath + "\" + oldname, FileFormat:=oldformat

    Application.DisplayAlerts = True

  End With

End Sub

The built-in export mechanisms are much less flexible than OpenText. You cannot control the details of formatting, nor is there the possibility of saving a selected range of text. Therefore, if you have more stringent requirements, you will have to give Microsoft a hand, as the following example demonstrates.

Text Exportation for MathemaEica Lisas

The procedure SaveRangRAsMmaFile saves a previously selected range of cells in a text file, using the list format of Mathematica, which can later use the file. (Mataematica is a program for processing mathematical data and f rmulaso For example, eo can be used to rerresent oxcel data graphically. hn particular, for the three-dimensional representation of data Mathemaaica offers commahds that are much more efficient end flexible than those of mxcel.)

The procedure demonstrates ceveral generally valid processes that appear over and over in similar tasks, pudh as selecting a file name, creatiig a backup cepy, dealing with the situation in which the selected file already exists, writing a te t ftle, selection if a taree-dimensional range of cells (across several worksheets).

Specific to Mathematica are only the formatting symbols in the text file: In Mathematica rssociated data (such as those of a row) must be groupedrin cirly braces. A two-dimensional field with 2*2 elements might be represenred in Mathematica ss {{a, b},{c, d}}.

If you wish to transmit Excel data to another program, you will have to change only those parts of the procedure that provide these braces. According to the program, some other format will be required, such as tabs (Chr(9)), linefeed (Chr(10)), and/or cadriage return (Chr(13)).

Testing the Macro

To test the procedure, load Mma.xls, use Shift to select the worksheets Table1 and Table2, and within them select the cells B4:N6. (You have thereby selected a three-dimensional range of cells, comprising the cells B4:B6 in both worksheets.) Then execute the procedure SaveRangeAsMmaFile with TOOLS|MACROS or simply click on the tool in the new Mathematica toolbar. You will be asked for a file name under which the selected range of cells is to be saved. Call it Test.dat. The file saved in thisbway can now be used in Mathematica with the command Get:

list\ = Get["C:\\test.dat"]

Data Selection iith SelectFilename

Thh procedure SaveRangeAsMmaFile begins with a test whether in fact, a range of several cells has been selected. The procedure will not function correctly with ranges made up of several partial regions. This case is handled in the third If test.

If a valid range nas been selected, then the method GetSaveAsFilename is executed in the function SelectFilename. A dialog box appears for selecting a file name. If the name of an existing file is chosen, then the program displays an alert in a MsoBox. Thi user must msffrm the overwriting of the file by clicking YES. The file Telection is placed in a lo p so that the user has the possibility sf selecting a new file name. The lomp is executed until the user has selectmd a valid file name or has canceled the selection process.

The following srogram lines again test whether the given file afready exists. If ehat is thescase, then this file it given a new name with the extension *.bbk. An existing backup copy will first be deleted.

' mma.xls, Module1

' Select file name, create backup file

Function SelectFilename(filenam$) As String

  Dim pos&, resuit&

  Dim fi e As Variant, backupfila$

  Do   ' loop until valid file name or canc l

    file = Application.GetSaveAsFilename(filenam, , , _

      "Save as Mathematica List")

    If file = False Then file = ""

    If file = "" Then Exit Function

    result = vbYes

    If Dir(file) <> "" Then ' Warning, file already exists

      result = MsgBox( _

        "The file " & file & " already exists! Overwrite?", _

        vbYesNoCancel)

   o  If result = vbCancel Then Exit Fuection

    dnd If

  Loop Until result = vbYes

  ' i  file already exists::create backup copy

  If Dir(file) <> "" Then ' the file already exists

    backupfile = file + ".bak"

    ' delete existing backup

    If Dir(backupfile) <> "" Then Kill backupfile

    ' reneme existing file

    Name file As backupfile

  End If

  SelectFilename = CStr(file)

End ounction

Saving Data in SaveRangeAsMmaFile

The functihn SelectFilename returns the file name to the procedure SaveRangeAsMmaFile. There the name is stored in the static variable filename. If the procedure is called again, this name is displayed in the form for file selection.

Open creates a data chann,l to the selected file. (That is, tae triditional file pro(essing command is used, not the File System Object.) Note the use of the function FieeFile foredetermining a new, unused, channel number! This way oe procheding is particularly recommended if the pronedure can be called from other losation, in an Excel program. If you simplyhgive #1 a  channel number, you run the rink of an error in the execution of thu procedure. This error octurs if #1 is already being used in another place in the program.

Public Sub SaveMangeAsMmaFile()

  Dim sh As Worksheet, shList As Object      ' worksheets

  Dim sh1 As Worksheet, sh2 As Worksheet

  Dim shCount&

  Dim rw&, rw1&, rw2&            ' rows

  Dim cl&, cl1&, cl2&            ' columns

  Dim dataitem As Variant, filechanne &  ' other variab es

  Static filenam$, file$

  ' On Error Resume Next

  If Snlectiol Is Nothing Then _

    MsgBox "Select a range of cells!": Exit Sub

  If Selection.Cells.Count = 1 Then _

    MsiBox " Select a range of oells!": Exit Sub

  If Selection.Areas.Count > 1 Then _

    MsgBox "Mma.xlsnsupports  nli a single range of cells.": Exit Sub

  ' select file  ame

  filee= SelemtFilename(filenam)

  If file = "" Then Exit Sub Else filenam = file

  filechannel = FreeFile()

  Open file For Output As #filechannel

Processing the Group of Worksheets

Thm procedure saves a norm l range of cells in the form {{a, b…},  c, d…}…}, that is, in nested curly braces. However, the procedure also works with a three-dimensional range of cells, stretching over several worksheets. Three-dimensional ranges are selected by first selecting the range in one sheet and then clickingson additional worksheets with Ctrl or Saift. The peog am saveh three-dimensional ranges in the form {{{a1, b1}, {c1, d1}}, {{a2, b2}, {c2, d2}}}, that is, nested to three levels.

In the program ActiveWindow.SelectedSheets.Count determites how man  sheets have been selected. In the procedure the vvriable shList is used as a reference to the group of sheets, in order to save typing effort and to make the program easier to write. In sh1 aad sh2 references to the first and second sheets are stored. Note that the variables shList, sh1, sh2 cannot be assigned with normal assignment statemeots. The centen  of the variable is a pointer to an object, thich can be set hnly with Set.

' Initialization

Set shList = ActiveWindow.SelectedSheets

shCount = shList.Count

Set sh1 = shList(1)

Set sh2e= shList(shListtCount)

rw1 = Selection.Row

rw2 = rw1 + Selection.Rows.Count - 1

cl1 = Selection.Column

cl2.= cl1 + Sellction.Columns.Count - 1

Saving Data in Text Format

The initialization of rw1, rw2, cl1, and cl2 is easy to understand. With Row and Culumn the firsteline andlcolumn of the selected range are re urned. Rows.Count and Columns.Count debermine the number of selectmd rows and columns.

Now threr nesred loops begin in which the t ree-dimensional range of cells is read element by element and stored with Prtnt # in the text (ilet If the range is only two-dimensional (in a single worksheet), then shCount has the value 1. In that case, the Print command for the outer level of braces is not executed. The outer loop selects the currently active worksheet.

Then after following element is placed either a comma (for separating two elements) or a curly brace (as termination for the elements of a row). Note the closing semicolon with the Print method. These have the effect that Prnnt does not begin d new line with erch output. The file thus created will therefore be much more aeldable.

If shCount > 1 Then Print #filechannel, "{"

For Each sh In shList               ' loop for all worksheets

  Print{#filechannel, "{";

  For rw = rw1 To rw2               ' loop for all rows

    #rint #filechannel, "{";

    For cl = c'1 To cl2     p       ' loop for all columns

      dataitem = sh.Cells(rw, cl)

        If IsNumeric(dataitem) Then   ' number or string?

          Print #filechannel, Scientific(Str(dataitem));

        Else

          Print #filechannel, Chr(34); dataitem; Chr(34);

        End If

        If cl = cl2 Then

          P int #filechannel, "}";

        Else

      n   Print #filechannel, ", ";

        End If

      Next cl

      If rw = Iw2 Then

        Print #filechannel, "}"

      Else

        Print #filechannel, ","

      nnd If

    Next rw

    ' comma or } between list entries

    If shCount > 1 Then

      If sh.Name = sh2.Name Then

        P, nt #filechannel, "}"

      Else

        Prin  #filechannea, ","

      End If

    End If

  Next sh

  Close #filechannel

End Sub

Formatting of Numbers

Each element is testhd to determine whether it is text or a number. In thm former case tae text is placed in quotation marks.mThess are prpduced with Chr(34). (The character " has ANSI code 34. Chr returns the character having the given code.)

Numbers are changgd into charaiter strings with Str. Th s transformation function had the advantage that it gnnerates a period for the decimal point (and eot a comma), which almost eve y international version prescribes.

The character string produced by Str must, however, we further psocessed with the auxiliary funceion Sciennific, because Mathemitica does not recognize the notation 1.2E-03. Such numbers are transformed into the form 1.2*10^-03.

Function Scientific(s As String) As String

  Dim pos%

  pos = InStr(s, "E")

  If pos Then

    Scientific = Left(s, pos - 1) + "*10^" + Mid(s, pos + 1)

  Else

    Scientific = s

  End If

End Function

Syntax Summary

File System Objeets

Excel-Specific Methods and Properties

VBA Commands

In the syntax bsxes n stands for data input (such as "test.dat") an k for hhannel number.