5.6 Working with Files

5.6 Working with Files; Text Import and Export

This section describes commands and techniques for working with files. The need to read from or write to files appears in practice when you wish to exchange data with external programs.

The commands, methods , and properties in Excel programming for manipulation of files come from three different libraries, which explains a great deal of the resulting confusion.

• The Microsoft Scripting Library makes possible object-oriented access to files, directories, and text files with File System Objects (FSO). This library is available to programmers for the first time with Excel 2000. (Those who also work with the programming language Visual Basic or with the Windows Scripting Host have known about this library for a longer time.)

• Since Excel 5 the commands integrated into the VBA library, with which not only text files but binary files as well can be processed , have been available.

• 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 introduce a procedure with which you can save a 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.

File System Objects: An Overview

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.

Tip  

In this section the new File System Objects take center stage. The traditional commands will be extensively described only to the extent that they offer functionality that is lacking in the new objects. (The syntax summary at the end of this section is, however, complete.)

Using the FSO Library

If you wish to use the FSO library in your program, you must first activate the Microsoft Scripting Runtime library via TOOLSREFERENCES. The base object of this library is the FileSystemObject . This object is the starting point for various methods by which Drive[s], File[s], Folder[s], and TextStream objects can be generated. For this reason it is often useful to define a global FileSystemObject variable with Dim As 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 Folder 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 properties like Attributes , Name , Path , and Size you can determine various features of the addressed files and directories.

Tip  

In contrast to most other enumerations, with Drives , Files , and Folders access to individual elements via Files(n) is not possible. As index only the name of the particular object can be used. Since as a rule this is not known in advance, you must work with a For “Each loop.

Methods for generating or editing new directories and files are directly subordinate to the FileSystemObject , for example, CopyFile , CreateFolder , DeleteFile .

FILESYSTEMOBJECT : OBJECT HIERARCHY

DRIVE: OBJECT HIERARCHY

FOLDER: OBJECT HIERARCHY

FILE: OBJECT HIERARCHY

Drives, Folders, and Files

Properties of Drives (Drive Object)

A list of all available drives can easily be obtained via the enumeration fso. Drives (see Figure 5-5). The properties of the associated Drive objects give information about the characteristics of the drive: VolumeName (name), ShareName (name under which the drive is known in a network), TotalSize and FreeSpace (total and free space), FileSystem (the file system type as a character string, such as "FAT" , "NTFS" , or "CDFS" ), and DriveType ( Fixed , Remote , Removable , etc.).

Figure 5-5: Information on all accessible drives

Tip  

The Drives listing contains only local drives (and network drives associated with the drive's letter). On the other hand, possibly accessible network directories are not included.

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 , thanks to On Error .

 ' example 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  btnShowDrives_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 Each dr In fso.Drives     rng.Cells(i, 1) = dr     rng.Cells(i, 2) = FormatNumber(dr.AvailableSpace / 1024 ^ 2, 1) & _       " MB free"     rng.Cells(i, 3) = " [" & dr.VolumeName & ", " & _        dr.FileSystem & "] "     i = i + 1   Next End Sub 

The Current Directory

Although you will search in vain among the FSO objects for a CurrentDir property, this program information is considered , for example, in the method GetFolder . Thus fso.GetFolder(".").Path returns the path of the 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: ChDrive changes the current drive, ChDir changes the current directory, and CurDir returns the current directory (together with the drive).

Caution  

To change the current directory, ChDir is usually insufficient. The drive must also be changed. Therefore, the usual sequence of commands is as follows :

 path = "d:\backup"   ChDrive path   ChDir path 

However, if path points to a network directory ( \\server\share\ ), then there are problems. ChDrive does not work with network directories and triggers an error. (This can easily be overcome with On Error Resume Next.) Indeed, ChDir changes the current directory, but only when the network directory is considered the current drive (for example, at the start of a compiled Visual Basic program that resides on a network server). If that is not the case, then there exists under Visual Basic no possibility to make a network directory the current directory!

Tip  

In addition to the current directory there is a host of Excel-specific directories whose paths can be determined by means of various properties of the Excel Application object. See below under Excel-specific methods and properties.

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 Windows folder. The associated Folder object can easily be generated with fso. GetSpecialFolder (TemporaryFolder) . (Visual Basic recognizes two further folders: the Windows folder and the Windows system folder. Access is via GetSpecialFolder . As parameter you use WindowsFolder , respectively SystemFolder .)

Tip  

You can, moreover, easily determine the location of the temporary folder without FSO objects: The path to this folder is contained in the system variable TEMP.You can access this variable via the VBA function Environ("temp") (for example, C:\Winnt\Temp ). Similarly with Environ("windir"), you can easily determine the Windows directory (for example, C:\Winnt ).

Tip  

If you need not only the name of the temporary folder but also a suggestion for a valid (not already used) file name within it, you simply use fso. GetTempName() . However, this method returns only the name; you still must determine the associated directory via GetSpecialFolder .

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 of properties can be accessed: Name contains the name of the folder (in the example above, "system32" ), while the default property Path contains the complete path, including the drive. If communication with old DOS or Windows 3.1 program names according to the 8+3 character convention is necessary, these can be determined with ShortName and ShortPath .

DateCreated , DateLastAccessed , and DateLastModified 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 give the string "Dateiordner". (The Type property is more useful with File , where with known file extensions the data type is given.)

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

With IsRootFolder 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 object) be determined by means 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 ".." are included.

Size determines the space requirement of a folder and recursively goes through all subfolders. For this reason the determination of this property can take considerable time. Do not use this property unnecessarily!

Tip  

The resulting value contains the sum of the number of bytes of all files. In fact, the space on the hard drive is usually larger, because files are always saved in sectors. (A file containing three bytes thus requires, depending on the file system, one or more kilobytes of hard drive capacity.) However, the actual space requirement can also be smaller, namely, when the files (for example, in an NT file system) are compressed. Therefore, take the result of Size with a grain of salt!

Tip  

Most of these properties are read-only, and cannot be altered . The only exceptions are Attributes and Name .

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 Folders 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 such tests yourself inside a loop.

Files. Count 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 character string that also is displayed in Explorer in the type column.

Creating, Moving, Copying, and Deleting Files and Folders

With fso. CreateFolder 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 be described in detail in the section after next.

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

With the Copy operations you can use an optional parameter Overwrite to declare whether existing files and folders are to be overwritten. Caution: The default setting is True , meaning that existing files and folders will be overwritten without further ado! If you give the value False , then you will encounter error 58 (file already exists), which you can intercept with On Error .

The Move methods can likewise be used to change the name of a file or folder, or to move the object to another location (even to another drive). This operation will be carried out only if the target file or folder does not already exist. (This security feature cannot be changed with optional parameters.)

For Delete operations there again exists an optional parameter Force , which determines whether 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.

Recursively 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  processFile  (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     processFile subfld ' recursive call for all subfolders   Next End Sub 

Additional Helpful Functions

With 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 objects).

BuildPath (path, name)	forms a complete file name from path and name
GetAbsolutePath (name)	returns the complete file name when only a name relative to the current folder is given
GetBaseName (name)	returns the simple file name (without folder or drive)
GetDriveName (name)	returns the drive name
GetFileName (name)	like GetBaseName
GetParentFolderName (name)	returns the directory (including the drive but without the file name)

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

DriveExists (name)	tests whether drive exists (True / False)
FileExists (name)	tests whether file exists
FolderExists (name)	tests whether folder exists

Text Files (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.CreateTextFile(name$ [, overwrite, unicode]) Set ts = file.OpenAsTextStream([mode, unicode]) 

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

The parameter mode tells whether the file is open for reading ( ForReading , default setting), writing ( ForWriting ), 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 methods the selection is made with a Boolean value: False for ANSI (default setting) or True for Unicode. With the Open methods the parameter can take three values: TristateFalse for ANSI (default setting), TristateTrue 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 object. For reading files we have Read , ReadLine , and ReadAll . With these a given number of characters , a line, or the entire text is read and returned as a character string. With Skip and SkipLine you can skip over individual characters or an entire line. With AtEndOfLine and AtEndOfStream you can determine whether the end of a line or the end of the file has been reached. Line and Column give the current line and column numbers .

For writing text we have Write and WriteLine . The only difference between these two methods is that with WriteLine an end of line character is automatically inserted. WriteBlankLines creates a number of blank lines.

File operations should be terminated with Close . (At the end of a program or when the TextStream 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. ReadTextFile reads the file line by line and shows the contents in a MsgBox . DeleteTextFile then deletes the file.

 ' example file Files.xls, Module1 Option Explicit Public fso As New FileSystemObject Dim filname$ ' create text file Private Sub  CreateTextFile  ()   Dim i&, tstream As TextStream   With fso     filname = .BuildPath(.GetSpecialFolder(TemporaryFolder), _        .GetTempName)     Set tstream = .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     Next     .WriteLine "end of the file"     .Close   End With   MsgBox "temporary file " & filname End Sub ' read text file Private Sub  ReadTextFile  ()   Dim tstream As TextStream   Dim txt$   If filname = "" Then Exit Sub   Set tstream = fso.OpenTextFile(filname)   With tstream     While Not .AtEndOfStream       txt = txt + .ReadLine() + vbCrLf     Wend   End With   MsgBox "content of the file:" & vbCrLf & txt End Sub ' end of program; delete temporary file Private Sub  DeleteTextFile  ()   If filname = "" Then Exit Sub   fso.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:

Open	open file
Close	close file
Reset	close all open files
FreeFile	returns the next free file handle
Print , Write	write data in text mode
Input , Line Input	read data in text mode
Put	write data in binary or random access mode
Get	read data in binary or random access mode
LOF	returns length of file
EOF	tells whether end of file has been reached
Loc	location ”gives the current position of the file pointer
Seek	changes or reads the current position of the file pointer

In executing Open the purpose of the file access must be given in addition to the file name and file handle. Input , Output , and Append refer to text files and imply by their names the type of access ( Input : read-only; Output : write only; Append : read/write). Binary is intended for access to binary data and enables both reading and writing of data. Optionally, with Binary you can restrict the type of access to read or write. The following seven lines demonstrate the useful variants of the Open command.

 Open "datname" For Input As #1                    '(1) Open "datname" For Output As #1                   '(2) Open "datname" For Append As #1                   '(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 several files are to be opened at the same time, then each file must be given a unique file handle. The permissible range is from 1 to 511. The function FreeFile returns 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 file handle must be given. Reset closes all open files.

We proceed now to the commands that can be executed once a valid file handle is at hand. LOF (length of file) tells the size of the file. The function Loc (location) gives the current position of the file pointer, whether for reading or writing. This position locates which byte of the file is the next to be read or changed. (The smallest value is 1, not 0). 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) determines whether the end of the file has been reached (in which case Loc and LOF coincide).

Text 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 created. With the Open command the file handle 1 will be used. All further access to this file will be achieved with this file handle. To store data as ANSI text the command Print # will be used ( 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" Close #1 

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

 Dim lineOfTxt$ 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 . However, Input reads only up to the next comma. That is, lines containing commas are read in several pieces. Thus the Input command is particularly suitable for reading numbers into numeric variables .

Tip  

Although Visual Basic has used Unicode internally since Version 4, the traditional file operations have been carried out in ANSI format (with one byte per character). The conversion between Unicode and ANSI takes place automatically; Unicode characters for which there is no ANSI code cause problems, as could be expected. For reading and writing of Unicode files you should therefore use the TextStream object.

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 CreateBinaryFile the binary file is opened, and in LoadBinaryFile it is read. In the binary file square roots of numbers between 1 and 100 are stored as Double values. In LoadBinaryFile three values are read from the file (the square roots of 15, 16, and 17).

 ' example file Files.xls, Module2 ' example for traditional commands for working with files ' create temporary binary file test.bin Private Sub  CreateBinaryFile  ()   Dim sq As Double, i   Open Environ("temp") + "\test.bin" For Binary As #1   For i = 1 To 100     sq = Sqr(i)     Put #1, , sq   Next i   Close #1 End Sub ' open file, display the first three values in the immediate window Private Sub  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  ()   Kill Environ("temp") + "\test.bin" End Sub 

For loading and storing data in binary format we have the commands Get and 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 Test.bin has length 800 bytes (100 numerical values of eight bytes each, which is what is required for a Double number).

Similar to Put is 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 Double values are read from the file into the variable dbl and output to the screen.

Variant Variables in Binary Files

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

The result is that you should avoid using Variant variables if you wish 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!)

Character Strings in Binary Files

With character strings the problem arises that Put stores only the content, and not the length. Get can therefore not know where the character string to be read ends. For this reason Get reads exactly as many characters as at that moment are to be found in the variable. That, however, is no solution to the problem.

The correct way to proceed with the binary storage of character strings with variable length is first to store the length of the character string as a Long variable and then to store the content of the character string.

 Dim strlen&, mystr$ mystr = "123" strlen = Len(mystr) Put #1, , strlen Put #1, , mystr 

In loading you determine first the length of the character string and then initialize the character string before you execute Get :

 Get #1, , strlen mystr = Space(strlen) Get #1, , mystr 

If character strings appear in fields or in user -defined data types, then Get and Put incidentally take care of these administrative details 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 section.)

User-Defined Data Types and Fields in Binary Files

You can also use Get and Put for the efficient storage of fields and user-defined data types. In the case of fields, particularly, there seems to be a possible increase in program speed (over the previously necessary storage of each element individually).

Excel-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:\Documents and Settings\Administrator\My documents

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

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

• Application. StartupPath returns the personal XLStart folder: 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 ToolsOptions)

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

Caution  

The meaning of TemplatesPath and StartupPath has changed in Excel 2000 from that of previous versions. In Excel 97 these two properties returned the path to the global templates and the path to the Xlstart directories. However, in Excel 2000 these properties refer to the personal directories.

Unfortunately, there are no new properties to provide the global Xlstart folder that is simultaneously valid as global template folder. The following instruction has the drawback that it is region-specific (and in this form functions only for the English version of Excel; in the German version, for example, the Library directory has the name Makro ):

 globalxlstart = Replace(LCase(Application.LibraryPath), _         "library", "xlstart") 

Tip  

You will find an overview of the meaning of the Excel configuration files in the section on configuration files.

File Selection Form (GetOpenFilename, GetSaveAsFilename)

The two methods GetOpenFilename and GetSaveAsFilename display a dialog box to select an already existing file, respectively to name a new file. GetOpenFilename 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 *.xl? ) 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:

 ?Application.GetSaveAsFilename("name.xls", _   "Textfiles (*.txt), *.txt, Excel-files (*.xl?), *.xl?", 2) 

Tip  

GetOpen - and GetSaveAsFilename determine only one file name. However, no Excel file is opened or saved. For this there exist some methods that have already been introduced in this chapter under the subject of workbooks.

File Selection Form (FileDialog)

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 object to select a directory, which 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 desired (for example, SAVE AS). Now various properties of the object can be preset. Show displays the dialog. The return value True means that the selection was terminated with OK. Now the selected file or directory names can be read from the enumeration SelectedItems .

 Dim fd As FileDialog ' generate FileDialog Object for directory selection Set fd = Application.FileDialog(msoFileDialogFolderPicker) ' display with Show; True, if OK was pressed If fd.Show() = True Then   MsgBox "Selected Directory: " + fd.SelectedItems(1) End If 

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).

Tip  

If your external data are not located in a text file, but in a database, then you should have a look at Chapter 12. This chapter describes, among other things, the ADO library, with which you can establish a link to database data and database servers in order to automate the import and export of data.

Text Import Wizard for Interactive Importing

Aid in reading such files is offered by the text import wizard. This assistant appears automatically as soon as you select a text file with FileOpen. In three steps you indicate how the columns of the text file are separated 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, 23 text without quotes (but with special characters)  23.456, 23 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 must push the button Advanced to tell whether settings other than period for decimal point and comma for thousands separator are being used.

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

For scientific.txt the wizard knows all by itself that the columns are separated by tabs. In the third step the date format must be set according to the region. The wizard recognizes various date formats, which vary in the order of day, month, and year (Figure 5-7). And indeed, both files are imported correctly (Figure 5-8).

Figure 5-7: Setting the date format in importing scientific.txt

Figure 5-8: The result of the importation

Importing Text, Variant 2

The importing we have done thus far is based on importation via FILEOPEN. It is interesting to note that the command DataGet External DataImport Text File offers a second option. Here the same wizard appears as before, so that one has the impression that internally the same commands are being executed, commands accessible from two different menus . This impression is incorrect:

• 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 DataRefresh 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 OpenText of the 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.

Caution  

With the macro recorder only those settings that differ from the current default settings will be written in code. If the procedure is later to be executed on another computer, where possibly a different default setting is in force, this can lead to problems. To ensure that the code is portable, you will usually have to enlarge the code.

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.txt 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, _   Other:=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:

Filename	expects a character string with the file name.
Origin	gives the format of the text (of line division, etc.). Possibilities are xlMacintosh, xlWindows , and xlMSDOS .
StartRow	tells at which line importation should begin. The default is 1. If you wish to skip over several header lines, simply give a correspondingly higher value. Some tables begin with one or more header lines, which may or may not be needed after the importation; StartRow helps to drop such lines.
DataType	tells how the data are organized: xlDelimited means that the columns are delimited by a unique symbol (such as tab); xlFixedWidth means that the columns begin at a fixed position (and any extra space is filled with the space character).
Tab	tells whether the columns are tab delimited ( True/False , only with DataType:=xlDelimited ).
Semicolon	as above, but column separator is the semicolon.
Comma	as above, but column separator is the comma.
Space	as above, but column separator is the space.
Other	as above, but column separator is given by some other character specified by OtherChar .
OtherChar	gives the column separator explicitly (only with DataType:= xlDelimited and Other:=True ).
ConsecutiveDelimiter	tells whether several column delimiters together are to be regarded as a unit. This usually makes sense only if the column separator is a space. The default setting is False .
TextQualifier	tells how character strings are indicated: by double quotation marks ( xlTextQualifierDoubleQuote ), single quotation marks ( xlTextQualifierSingleQuote ), or nothing at all ( xlTextQualifierNone ).
DecimalSeparator	gives the decimal point character (for example, "." ). Warning: If no character is given, then the system setting is used!
ThousandsSeparator	gives the character for the thousands separator (for example, "," ).
FieldInfo	expects as argument an encapsulated data field ( Array ). Each column is specified with Array(n, opt) ; n gives either the column number ( DataType:=xlDelimited ) or the column position ( DataType:=xlFixedWidth ); opt denotes the data type of the column. Possibilities are as follows: xlGeneralFormat (1) : standard xlTextFormat (2) : text xlSkipColumn (9) : skip column xlMDYFormat (3), xlDMYFormat (4), etc.: data format

If OpenText 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 Excel file is changed. ActiveWorkbook now refers to the target file! To close the import file you must use an object variable.

 ' example file Files.xls, Module3 ' Import / Export Sub  ImportScientific  ()   Dim fname$   Dim newworkb As Workbook   fname = ThisWorkbook.Path + "\scientific.txt"   Workbooks.OpenText Filename:=fname, ...   Set newworkb = ActiveWorkbook   newworkb.Worksheets(1).Copy after:=ThisWorkbook.Worksheets(1)   newworkb.Close False End Sub 

Tip  

With the macro recorder only those import settings are shown that are different from the default setting (regional setting!) If the code is later executed on a computer with a different regional setting, then unexpected problems could arise, since other default settings might be in use. If your code is designed for international use, you have to give all relevant settings explicitly, even those that are not automatically recorded by the macro recorder!

Tip  

If all the many settings available with OpenText are insufficient for importing a text file correctly, then you will have to program an importation procedure yourself. Essentially, you have to open the text file, read it into a character string variable line by line, and there analyze it. This might require some effort, but it is by no means all that complicated.

The Object QueryTable (Text Import, Variant 2)

In addition to FILEOPEN you can also use in Excel the command DATAGET EXTERNAL DATAIMPORT TEXT FILE. The importation is then carried out by the QueryTable object. This object is generally used by Excel to describe the import parameters of external data, which includes not only text files, but databases and web sites as well.

Pointer  

Here we are dealing only with importing text. Additional background information and a detailed description of database importation can be found in Chapter 12. Some special features of HTML importation are described in Chapter 14.

The first time you import via code, you must attach a new QueryTable 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 OpenText . Worthy of note is the property Name : It determines not only 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 Refresh . The following code was originally created with the macro recorder and then manually optimized and formatted for readability.

 Sub  ImportNewText  ()     'Files.xls, Module4   Dim qt As QueryTable, ws As Worksheet   Dim fname$   fname = ThisWorkbook.Path + "\scientific.txt"   Set ws = Worksheets("QueryTable")   ws.Activate ' delete existing QueryTables   For Each qt In ws.QueryTables     qt.Delete   Next   ws.Cells.ClearContents   ws.Range("A1").Select   ' create new QueryTable   Set qt = ws.QueryTables.Add("Text;" + fname, [A1])   With qt     .Name = "scientific"     .FieldNames = True     .RowNumbers = False     .FillAdjacentFormulas = False     .PreserveFormatting = True     .RefreshOnFileOpen = False     .RefreshStyle = xlInsertDeleteCells     .SavePassword = False     .SaveData = True     .AdjustColumnWidth = True     .RefreshPeriod = 0     .TextFilePromptOnRefresh = False     .TextFilePlatform = xlWindows     .TextFileStartRow = 1     .TextFileParseType = xlDelimited     .TextFileTextQualifier = xlTextQualifierDoubleQuote     .TextFileConsecutiveDelimiter = False     .TextFileTabDelimiter = True     .TextFileSemicolonDelimiter = False     .TextFileCommaDelimiter = False     .TextFileSpaceDelimiter = False     .TextFileColumnDataTypes = Array(1, 3, 1, 1)     .TextFileDecimalSeparator = "."     .TextFileThousandsSeparator = ","     .Refresh BackgroundQuery:=False   'here the importing is triggered   End With End Sub 

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

 Sub  RefreshImport  ()   Dim ws As Worksheet   Set ws = Worksheets("QueryTable")   ws.Activate   If ws.QueryTables.Count = 0 Then Exit Sub   With ws.QueryTables(1)     .TextFilePromptOnRefresh = False     .Refresh   End 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).

Tip  

There are many other formats for saving a file or worksheet, such as HTML document, Unicode file, or database (dBase format).

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 to a Worksheet object.

 ActiveWorkbook.ActiveSheet.SaveAs _   Filename:="c:\sample-file.csv", FileFormat:=xlCSV 

An unpleasant side effect of SaveAs is that the active name of the current file changes. For example, Files.xls is turned into Files.csv . If you then wish to save the entire file again as an Excel file, you must 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 alert asking whether the file may be overwritten.

The following procedure first saves the current worksheet in CSV format. Then the entire file (that is, the Workbook ) is saved under its original name and file type.

 ' Files.xls, Module3 Sub  ExportActiveWorksheet  ()   Dim oldname$, oldpath$, oldformat As XlFileFormat   With ActiveWorkbook     oldname = .Name     oldpath = .Path     oldformat = .FileFormat     .ActiveSheet.SaveAs _       Filename:="c:\sample-file.csv", FileFormat:=xlCSV     Application.DisplayAlerts = 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 Mathematica Lists

The procedure SaveRangeAsMmaFile saves a previously selected range of cells in a text file, using the list format of Mathematica, which can later use the file. ( Mathematica is a program for processing mathematical data and formulas. For example, it can be used to represent Excel data graphically. In particular, for the three-dimensional representation of data Mathematica offers commands that are much more efficient and flexible than those of Excel.)

The procedure demonstrates several generally valid processes that appear over and over in similar tasks , such as selecting a file name, creating a backup copy, dealing with the situation in which the selected file already exists, writing a text file, selection of a three-dimensional range of cells (across several worksheets).

Specific to Mathematica are only the formatting symbols in the text file: In Mathematica associated data (such as those of a row) must be grouped in curly braces. A two-dimensional field with 2*2 elements might be represented in Mathematica as {{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 carriage 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 TOOLSMACROS 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 this way can now be used in Mathematica with the command Get :

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

Data Selection with SelectFilename

The 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 has 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 MsgBox . The user must affirm the overwriting of the file by clicking YES. The file selection is placed in a loop so that the user has the possibility of selecting a new file name. The loop is executed until the user has selected a valid file name or has canceled the selection process.

The following program lines again test whether the given file already exists. If that is the case, then this file is given a new name with the extension *.bak . An existing backup copy will first be deleted.

 ' mma.xls, Module1 ' Select file name, create backup file Function  SelectFilename  (filenam$) As String   Dim pos&, result&   Dim file As Variant, backupfile$   Do   ' loop until valid file name or cancel     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)       If result = vbCancel Then Exit Function     End If   Loop Until result = vbYes   ' if 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     ' rename existing file     Name file As backupfile   End If   SelectFilename = CStr(file) End Function 

Saving Data in SaveRangeAsMmaFile

The function 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 channel to the selected file. (That is, the traditional file processing command is used, not the File System Object .) Note the use of the function FreeFile for determining a new, unused, channel number! This way of proceeding is particularly recommended if the procedure can be called from other locations in an Excel program. If you simply give #1 as channel number, you run the risk of an error in the execution of the procedure. This error occurs if #1 is already being used in another place in the program.

 Public Sub  SaveRangeAsMmaFile  ()   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, filechannel&  ' other variables   Static filenam$, file$   ' On Error Resume Next   If Selection Is Nothing Then _     MsgBox "Select a range of cells!": Exit Sub   If Selection.Cells.Count = 1 Then _     MsgBox " Select a range of cells!": Exit Sub   If Selection.Areas.Count > 1 Then _     MsgBox "Mma.xls supports only a single range of cells.": Exit Sub   ' select file name   file = SelectFilename(filenam)   If file = "" Then Exit Sub Else filenam = file   filechannel = FreeFile()   Open file For Output As #filechannel 

Processing the Group of Worksheets

The procedure saves a normal 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 clicking on additional worksheets with Ctrl or Shift. The program saves 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 determines how many sheets have been selected. In the procedure the variable 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 and sh2 references to the first and second sheets are stored. Note that the variables shList , sh1 , sh2 cannot be assigned with normal assignment statements. The content of the variable is a pointer to an object, which can be set only with Set .

 ' Initialization Set shList = ActiveWindow.SelectedSheets shCount = shList.Count Set sh1 = shList(1) Set sh2 = shList(shList.Count) rw1 = Selection.Row rw2 = rw1 + Selection.Rows.Count - 1 cl1 = Selection.Column cl2 = cl1 + Selection.Columns.Count - 1 

Saving Data in Text Format

The initialization of rw 1, rw2, cl1, and cl2 is easy to understand. With Row and Column the first line and column of the selected range are returned. Rows.Count and Columns.Count determine the number of selected rows and columns.

Now three nested loops begin in which the three-dimensional range of cells is read element by element and stored with Print # in the text file. 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 Print does not begin a new line with each output. The file thus created will therefore be much more readable.

 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     Print #filechannel, "{";     For cl = cl1 To cl2             ' 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           Print #filechannel, "}";         Else           Print #filechannel, ", ";         End If       Next cl       If rw = rw2 Then         Print #filechannel, "}"       Else         Print #filechannel, ","       End If     Next rw     ' comma or } between list entries     If shCount > 1 Then       If sh.Name = sh2.Name Then         Print #filechannel, "}"       Else         Print #filechannel, ","       End If     End If   Next sh   Close #filechannel End Sub 

Formatting of Numbers

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

Numbers are changed into character strings with Str . This transformation function has the advantage that it generates a period for the decimal point (and not a comma), which almost every international version prescribes.

The character string produced by Str must, however, be further processed with the auxiliary function Scientific , because Mathematica 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 Objects

FILESYSTEMOBJECT ”PROPERTY
Drives	refers to a list of all drives

FILESYSTEMOBJECT ”METHODS
CopyFile/-Folder	copy file or folder
DeleteFile/-Folder	delete file or folder
DriveExists(name)	tests whether drive exists
FileExists(name)	tests whether file exists
FolderExists(name)	tests whether folder exists
GetAbsolutePath(relname)	creates complete file name (from relative input)
GetBaseName(name)	returns simple name (without folder/drive)
GetDrive	returns Drive object
GetDriveName(name)	returns drive name
GetFile	returns File object
GetFileName(name)	like GetBaseName
GetFolder	returns Folder object
GetParentFolderName(name)	returns folder name (with drive)
GetSpecialFolder	returns Folder object for Windows (System)folder
GetTempName	returns name for temporary file (without folder!)
MoveFile / -Folder	move or rename file or folder
OpenTextFile	opens a text file

DRIVE ”PROPERTIES
AvailableSpace	free drive capacity
DriveType	drive type (e.g., Remote , CDRom )
FileSystem	file system (e.g., "NTFS" , "FAT" )
FreeSpace	like AvailableSpace
IsReady	ready (used with A: Disk)
Path	character string of the path without \ (e.g., "C:" )
RootFolder	reference to Folder object
ShareName	drive name on network
TotalSize	total capacity
VolumeName	volume name

FILE / FOLDER ”COMMON PROPERTIES
Attributes	attributes (write protected, compromised, etc.)
DateCreated	date and time of creation
DateLastAccessed	date and time of last access
DateLastChanged	date and time of last change
Drive	reference to drive ( Drive object)
Files	list of all contained files (only Folder )
IsRootFolder	True , if root folder (only Folder )
Name	name (without folder / drive)
ParentFolder	pointer to folder one level up ( Folder object)
Path	character string with complete name (including folder/drive)
ShortName	name in 8+3 convention (DOS/Windows 3.1)
ShortPath	path in 8+3 convention (DOS/Windows 3.1)
Size	file size or sum over contained files
SubFolders	list of all subfolders (only Folder )
Type	name of file type

FILE / FOLDER ”COMMON METHODS
Copy	copy file / folder
CreateTextFile	create text file (only Folder )
Delete	delete file / folder
Move	rename or move file / folder
OpenAsStream	open as text file (only File )

TEXTSTREAM ”PROPERTIES
AtEndOfLine	end of line reached?
AtEndOfStream	end of file reached?
Column	current position within a line
Line	current line number

TEXTSTREAM ”METHODS
Close	close file
Read	read n characters
ReadAll	read entire file into a character string
ReadLine	read next line
Skip	skip n characters
SkipLine	skip lines
Write	write character string (without line break character)
WriteLine	write one line (with line break character)
WriteBlankLines	write n blank lines

Excel-Specific Methods and Properties

DRIVES AND FOLDERS
ActiveWorkbook.Path	path of active workbook
ActiveWorkbook.Name	file name of active workbook
Application.Path	path to Excel.exe
Application.DefaultFilePath	path to workbook
Application.LibraryPath	path to global Library directory (folder)
Application.UserLibraryPath	path to personal add-in directory
Application.StartupPath	path to personal XLStart directory
Application.TemplatesPath	path to personal template directory
Application.AltStartupPath	path to additional autostart directory (can be set with TOOLSOPTIONS )

FILE SELECTION
Application.GetOpenFilename	file selection (open file, existing files only)
Application.GetSaveAsFilename	file selection (save file, with alert)

IMPORT / EXPORT
Workbooks.OpenText	import text file, variant 1
Worksheets( ).QueryTables.Add	import text file, variant 2
Worksheets( ).SaveAs	save worksheet in various formats

VBA Commands

In the syntax boxes n stands for data input (such as "test.dat" ) and k for channel number.

MANAGING FILES AND FOLDERS
CurDir	returns current folder
Environ("Temp")	returns folder for temporary file
ChDir n	changes current folder
ChDrive drv	changes current drive
MkDir n	creates new folder
RmDir n	deletes empty folder
Name n1 As n2	gives n1 the new name n2
FileCopy n1, n2	copies n1 to n2
Kill n	deletes the given file(s)
Dir(n [, attribute]) pattern	returns the first file that corresponds to the search
Dir	returns the next file or an empty character string
FileLen(n)	returns the length of n in bytes
FileDateTime(n)	returns date and time of last change
GetAttr(n)	returns the attribute (read-only, etc.) of n
SetAttr n, attr	changes the attribute of n

OPEN DATA CHANNEL
f = FreeFile	returns free data channel number open data channel to:
Open d For Input As #f	read text file
Open d For Output As #f	write text file
Open d For Append As #f	read and write text file
Open d For Binary As #f	read and write binary file
Open d For Binary Access Read As #f	read-only binary file
Open d For Binary Access Write As #f	write-only binary file
Open d For Random As #f Len=l	read and write random access file

PROCESS FILES VIA DATA CHANNEL
Close #f	close data channel
Reset	close all open data channels
EOF(n)	reached end of file?
LOF(n)	determine size of file
Loc(n)	determine current position of file pointer
Seek #f, position	change file pointer
Print #f, var1, var2	write line in text format
Write #f, var1, var2	as above, but with format character " and ,
Input #f, var1, var2	read variables
Line Input #f, var	read entire line
var = Input(n, #f)	read n characters
var = InputB(n, #f)	read n bytes
Put #f, , var	variable / field / etc. store as binary
Get #f, , var	read variable as binary