Performing Common File Operations

Excel provides two ways to perform common file operations:

• Use traditional VBA statements and functions. This method works for all versions of Excel.

• Use the FileSystemObject object, which uses the Microsoft Scripting Library. This method works for Excel 2000 and later.

NEW  

Previous versions of Excel also supported the use of the FileSearch object. That feature has been removed from Excel 2007. If you execute an old macro that uses the FileSearch object, the macro will fail.

In the sections that follow, I discuss these two methods and present examples.

Using VBA file- related commands

The VBA commands that you can use to work with files are summarized in Table 27-1. Most of these commands are straightforward, and all are described in the Help system.

Table 27-1: VBA FILE-RELATED COMMANDS
Open table as spreadsheet

Command	What It Does
ChDir	Changes the current directory.
ChDrive	Changes the current drive.
Dir	Returns a filename or directory that matches a specified pattern or file attribute.
FileCopy	Copies a file.
FileDateTime	Returns the date and time when a file was last modified.
FileLen	Returns the size of a file, in bytes.
GetAttr	Returns a value that represents an attribute of a file.
Kill	Deletes a file.
MkDir	Creates a new directory.
Name	Renames a file or directory.
RmDir	Removes an empty directory.
SetAttr	Changes an attribute for a file.

The remainder of this section consists of examples that demonstrate some of the file manipulation commands.

A VBA FUNCTION TO DETERMINE WHETHER A FILE EXISTS

The following function returns True if a particular file exists and False if it does not exist. If the Dir function returns an empty string, the file could not be found, so the function returns False .

 Function FileExists(fname) As Boolean     FileExists = Dir(fname) <> "" End Function 

The argument for the FileExists function consists of a full path and filename. The function can be either used in a worksheet or called from a VBA procedure.

A VBA FUNCTION TO DETERMINE WHETHER A PATH EXISTS

The following function returns True if a specified path exists and False otherwise :

 Function PathExists(pname) As Boolean '   Returns TRUE if the path exists     On Error Resume Next     PathExists = (GetAttr(pname) And vbDirectory) = vbDirectory End Function 

CD-ROM  

The FileExists and PathExists functions are available on the CD-ROM. The filename is  file functions.xlsm .

A VBA PROCEDURE TO DISPLAY A LIST OF FILES IN A DIRECTORY

The following procedure displays (in the active worksheet) a list of files contained in a particular directory, along with the file size and date:

 Sub ListFiles()     Dim Directory As String     Dim r As Long     Dim f As String     Directory = "f:\excelfiles\budgeting\"     r = 1 '   Insert headers     Cells(r, 1) = "FileName"     Cells(r, 2) = "Size"     Cells(r, 3) = "Date/Time"     Range("A1:C1").Font.Bold = True '   Get first file      f = Dir(Directory, vbReadOnly + vbHidden + vbSystem)     Do While f <> ""         r = r + 1         Cells(r, 1) = f         Cells(r, 2) = FileLen(Directory & f)         Cells(r, 3) = FileDateTime(Directory & f)     '   Get next file         f = Dir()     Loop End Sub 

Figure 27-1 shows an example of the output of the ListFiles subroutine.

Figure 27-1: Output from the ListFiles procedure.

Notice that the procedure uses the Dir function twice. The first time (used with an argument), it retrieves the first filename found. Subsequent calls (without an argument) retrieve additional filenames. When no more files are found, the Dir function returns an empty string.

CD-ROM  

The companion CD-ROM contains a version of this procedure that uses the GetDirectory function (described in Chapter 12), which allows you to select a directory from a dialog box. The filename is  create file list.xlsm .

The Dir function also accepts wildcard file specifications in its first argument. To get a list of Excel files, for example, you could use a statement such as this:

 f = Dir(Directory & "*.xl??", vbReadOnly + vbHidden + vbSystem) 

This statement retrieves the name of the first *.xl?? file in the specified directory. The wildcard specification returns a four-character extension that begins with XL. For example, the extension could be XLSX, XLTX, or XLAM. The second argument for the Dir function lets you specify the attributes of the files (in terms of built-in constants). In this example, the Dir function retrieves filenames that have no attributes, read-only files, hidden files, and system files.

Table 27-2. lists the built-in constants for the Dir function.

Table 27-2: FILE ATTRIBUTE CONSTANTS FOR THE DIR FUNCTION
Open table as spreadsheet

Constant	Value	Description
vbNormal		Files with no attributes. This is the default setting and is always in effect.
vbReadOnly	1	Read-only files.
vbHidden	2	Hidden files.
vbSystem	4	System files.
vbVolume	8	Volume label. If any other attribute is specified, this attribute is ignored.
vbDirectory	16	Directories. This attribute does not work. Calling the Dir function with the vbDirectory attribute does not continually return subdirectories.

Caution  

If you use the Dir function to loop through files and call another procedure to process the files, make sure the other procedure does not use the Dir function. Only one "set" of Dir calls can be active at any time.

A RECURSIVE VBA PROCEDURE TO DISPLAY A LIST OF FILES IN NESTED DIRECTORIES

The example in this section creates a list of files in a specified directory, including all of its subdirectories. This procedure is unusual because it calls itself - a concept known as recursion.

 Public Sub RecursiveDir(ByVal CurrDir As String, Optional ByVal Level As Long)     Dim Dirs() As String     Dim NumDirs As Long     Dim FileName As String     Dim PathAndName As String     Dim i As Long '   Make sure path ends in backslash     If Right(CurrDir, 1) <> "\" Then CurrDir = CurrDir & "\" '   Put column headings on active sheet     Cells(1, 1) = "Path"     Cells(1, 2) = "Filename"     Cells(1, 3) = "Size"     Cells(1, 4) = "Date/Time"     Range("A1:D1").Font.Bold = True '   Get files     FileName = Dir(CurrDir & "*.*", vbDirectory)     Do While Len(FileName) <> 0       If Left(FileName, 1) <> "." Then 'Current dir         PathAndName = CurrDir & FileName         If (GetAttr(PathAndName) And vbDirectory) = vbDirectory Then           'store found directories            ReDim Preserve Dirs(0 To NumDirs) As String            Dirs(NumDirs) = PathAndName            NumDirs = NumDirs + 1         Else           'Write the path and file to the sheet           Cells(WorksheetFunction.CountA(Range("A:A")) + 1, 1) = _              CurrDir           Cells(WorksheetFunction.CountA(Range("B:B")) + 1, 2) = _              FileName           Cells(WorksheetFunction.CountA(Range("C:C")) + 1, 3) = _              FileLen(PathAndName)           Cells(WorksheetFunction.CountA(Range("D:D")) + 1, 4) = _              FileDateTime(PathAndName)         End If     End If         FileName = Dir()     Loop     ' Process the found directories, recursively     For i = 0 To NumDirs - 1         RecursiveDir Dirs(i), Level + 2     Next i End Sub 

The procedure takes one argument, CurrDir , which is the directory being examined. Information for each file is displayed in the active worksheet. As the procedure loops through the files, it stores the subdirectory names in an array named Dirs . When no more files are found, the procedure calls itself using an entry in the Dirs array for its argument. When all of the directories in the Dirs array have been processed , the procedure ends.

Because the RecursiveDir procedure uses an argument, it must be executed from another procedure by using a statement like this:

 Call RecursiveDir("c:\directory\") 

CD-ROM  

The companion CD-ROM contains a version of this procedure that allows you to select a directory from a dialog box. The filename is  recursive file list.xlsm .

Using the FileSystemObject object

The FileSystemObject object is a member of the Windows Scripting Host and provides access to a computer's file system. This object is often used in script-oriented Web pages (for example, VBScript and JavaScript) and can be used with Excel 2000 and later versions.

Caution  

The Windows Scripting Host is sometimes used as a way to spread computer viruses. Consequently, the Windows Scripting Host may be disabled on some systems. Therefore, use caution if you are designing an application that will be used on many different systems.

The name FileSystemObject is a bit misleading because it actually includes a number of objects, each designed for a specific purpose:

• Drive: Represents a drive or a collection of drives .

• File: Represents a file or a collection of files.

• Folder: Represents a folder or a collection of folders.

• TextStream: Represents a stream of text that is read from, written to, or appended to a text file.

The first step in using the FileSystemObject object is to create an instance of the object. This can be done in two ways: early binding and late binding.

The late binding method uses two statements, like this:

 Dim FileSys As Object     Set FileSys = CreateObject("Scripting.FileSystemObject") 

Note that the FileSys object variable is declared as a generic Object rather than as an actual object type. The object type is resolved at runtime.

The early binding method of creating the object requires that you set up a reference to the Windows Scripting Host Object Model. You do this by using Tools References in the VBE (see Figure 27-2). After you've established the reference, create the object by using statements like these:

Figure 27-2: Creating a reference to the Windows Script Host Object Model.

 Dim FileSys As FileSystemObject Set FileSys = CreateObject("Scripting.FileSystemObject") 

Using the early binding method enables you to take advantage of the VBE's Auto List Members feature to help you identify properties and methods as you type. In addition, you can use the Object Browser (by pressing F2) to learn more about the object model.

The examples that follow demonstrate various tasks using the FileSystemObject object.

USING FILESYSTEMOBJECT TO DETERMINE WHETHER A FILE EXISTS

The Function procedure that follows accepts one argument (the path and filename) and returns True if the file exists:

 Function FileExists3(fname) As Boolean     Dim FileSys As Object 'FileSystemObject     Set FileSys = CreateObject("Scripting.FileSystemObject")     FileExists3 = FileSys.FileExists(fname)     End Function 

The function creates a new FileSystemObject object named FileSys and then accesses the FileExists property for that object.

USING FILESYSTEMOBJECT TO DETERMINE WHETHER A PATH EXISTS

The Function procedure that follows accepts one argument (the path) and returns True if the path exists:

 Function PathExists2(path) As Boolean     Dim FileSys As Object 'FileSystemObject     Dim FolderObj As Object 'Folder     Set FileSys = CreateObject("Scripting.FileSystemObject")     On Error Resume Next     Set FolderObj = FileSys.GetFolder(path)     PathExists2 = Err = 0 End Function 

The function attempts to create a reference to a Folder object named FolderObj . If this operation is successful, the directory exists. If an error occurs, the directory does not exist.

USING FILESYSTEMOBJECT TO LIST INFORMATION ABOUT ALL AVAILABLE DISK DRIVES

The example in this section uses FileSystemObject to retrieve and display information about all disk drives. The procedure loops through the Drives collection and writes various property values to a worksheet.

Figure 27-3 shows the results run on a system with six drives. The data shown is the drive letter, whether the drive is "ready," the drive type, the volume name, the total size, and the available space.

Figure 27-3: Output from the ShowDriveInfo procedure.

CD-ROM  

This workbook, named  show drive info .xlsm , is available on the companion CD-ROM.

 Sub ShowDriveInfo()     Dim FileSys As FileSystemObject     Dim Drv As Drive      Dim Row As Long     Set FileSys = CreateObject("Scripting.FileSystemObject")     Cells.ClearContents     Row = 1 '   Column headers     Range("A1:F1") = Array("Drive", "Ready", "Type", "Vol. Name", _       "Size", "Available")     On Error Resume Next '   Loop through the drives     For Each Drv In FileSys.Drives         Row = Row + 1         Cells(Row, 1) = Drv.DriveLetter         Cells(Row, 2) = Drv.IsReady         Select Case Drv.DriveType             Case 0: Cells(Row, 3) = "Unknown"             Case 1: Cells(Row, 3) = "Removable"             Case 2: Cells(Row, 3) = "Fixed"             Case 3: Cells(Row, 3) = "Network"             Case 4: Cells(Row, 3) = "CD-ROM"             Case 5: Cells(Row, 3) = "RAM Disk"         End Select         Cells(Row, 4) = Drv.VolumeName         Cells(Row, 5) = Drv.TotalSize         Cells(Row, 6) = Drv.AvailableSpace     Next Drv     'Make a table     ActiveSheet.ListObjects.Add xlSrcRange, _       Range("A1").CurrentRegion, , xlYes End Sub 

CROSS REFERENCE  

Chapter 11 describes another method of getting drive information by using Windows API functions.