Working with Files, Folders, and Drives: More VBA Tips and Tricks

by David Shank
Microsoft Corporation

September 7, 2000

In last month's column, I discussed how to use Visual Basic for Applications (VBA) when working with strings and dates in your custom Office solutions. This month, I continue my discussion of VBA, this time talking about how to use VBA and Office technologies to work with files and the file system.

If you want to work with files or the file system, you have several options available—and the best choice depends on what specifically you want to accomplish. The available options include using VBA functions, the Microsoft Scripting Runtime Object Library, the Office FileSearch object, and file system-related Windows API functions. The only things I will not cover are the Windows API functions.

Working with the VBA Functions

You can use several VBA functions to work with the file system. These functions are summarized in the following table:

I will discuss the Dir and GetAttr functions in the following sections. You can find the complete documentation for each of these language elements in the VBA online language reference.

How do I use the Dir function to determine whether a file exists?

The Dir function returns the name of the file specified in its pathname argument. You typically use the Dir function to determine whether a specified file exists as illustrated in the DoesFileExist function:

Function DoesFileExist(strFileSpec As String) As Boolean
    ' Return True if file specified in the
    ' strFilespec argument exists.
    ' Return False if strFileSpec is not a valid
    ' file or if strFileSpec is a directory.
    Const INVALID_ARGUMENT As Long = 53
    On Error GoTo DoesfileExist_Err
    If (GetAttr(strFileSpec) And vbDirectory) <> vbDirectory Then
        DoesFileExist = CBool(Len(Dir(strFileSpec)) > 0)
    Else
        DoesFileExist = False
    End If
DoesfileExist_End:
    Exit Function
DoesfileExist_Err:
    DoesFileExist = False
    Resume DoesfileExist_End
End Function

In this example, the GetAttr function is used to ensure that the value in the strFileSpec argument is not a directory. This is because if you pass a valid directory name to the Dir function, it will return the first file it finds in that directory.

How do I use the Dir function to get the names of all files in a folder?

If the pathname argument contains the path to a folder instead of the name of a file in a folder, Dir returns the name of the first file it finds in the folder. You then call Dir again, without any arguments, to get the name of each subsequent file in the folder. For example, the following procedure returns an array that contains the names of all files in the directory specified in the strDirPath argument:

Function GetAllFilesInDir(ByVal strDirPath As String) As Variant
    ' Loop through the directory specified in strDirPath and save each
    ' file name in an array, then return that array to the calling
    ' procedure.
    ' Return False if strDirPath is not a valid directory.
    Dim strTempName As String
    Dim varFiles() As Variant
    Dim lngFileCount As Long
    
    On Error GoTo GetAllFiles_Err
    
    ' Make sure that strDirPath ends with a "\" character.
    If Right$(strDirPath, 1) <> "\" Then
        strDirPath = strDirPath & "\"
    End If
    
    ' Make sure strDirPath is a directory.
    If GetAttr(strDirPath) = vbDirectory Then
        strTempName = Dir(strDirPath, vbDirectory)
        Do Until Len(strTempName) = 0
            ' Exclude ".", "..".
            If (strTempName <> ".") And (strTempName <> "..") Then
                ' Make sure we do not have a sub-directory name.
                If (GetAttr(strDirPath & strTempName) _
                    And vbDirectory) <> vbDirectory Then
                    ' Increase the size of the array
                    ' to accommodate the found filename
                    ' and add the filename to the array.
                    ReDim Preserve varFiles(lngFileCount)
                    varFiles(lngFileCount) = strTempName
                    lngFileCount = lngFileCount + 1
                End If
            End If
            ' Use the Dir function to find the next filename.
            strTempName = Dir()
        Loop
        ' Return the array of found files.
        GetAllFilesInDir = varFiles
    End If
GetAllFiles_End:
    Exit Function
GetAllFiles_Err:
    GetAllFilesInDir = False
    Resume GetAllFiles_End
End Function

The GetAllFilesInDir function works by looping through each item in a directory and, for each item that is a file, adding the file name to an array. The first time Dir is called, it uses a directory name as its argument. Each additional call uses Dir without arguments. The procedure uses the GetAttr function to make sure that the strDirPath argument contains a valid directory. It also uses the GetAttr function to prevent the names of any subdirectories being added to the array. Notice also that the procedure filters out the "." and "..", which represent the current directory and the parent directory.

You can use the following procedure to test the GetAllFilesInDir procedure. You can try different values for the strDirName argument, then use F8 to step through the code as it executes to see how the procedure works.

Sub TestGetAllFiles()
    Dim varFileArray As Variant
    Dim lngI As Long
    Dim strDirName As String
    
    Const NO_FILES_IN_DIR As Long = 9
    Const INVALID_DIR As Long = 13
    
    On Error GoTo Test_Err
    
    strDirName = "c:\my documents"
    varFileArray = GetAllFilesInDir(strDirName)
    For lngI = 0 To UBound(varFileArray)
        Debug.Print varFileArray(lngI)
    Next lngI

Test_Err:
    Select Case Err.Number
        Case NO_FILES_IN_DIR
            MsgBox "The directory named '" & strDirName _
                & "' contains no files."
        Case INVALID_DIR
            MsgBox "'" & strDirName & "' is not a valid directory."
        Case 0
        Case Else
            MsgBox "Error #" & Err.Number & " - " & Err.Description
    End Select
End Sub

For additional examples that use the Dir function, see HOWTO: Search Directories to Find or List Files (https://support.microsoft.com/support/kb/articles/Q185/4/76.asp).

Working with the Microsoft Scripting Runtime Object Library

The Microsoft Scripting Runtime object library is installed with Microsoft Office 2000 and contains objects you can use to work with files and directories. If you have access to it, the object library is easier to use than the VBA functions described in the previous section.

You must set a reference to this library before you can use it. If Microsoft Scripting Runtime does not appear in the References dialog box (Tools menu), you should be able to find it in the C:\Windows\System subfolder as Scrrun.dll.

The following table describes the objects contained in the Scripting Runtime library.

The top-level objects in the Scripting Runtime object library are the Dictionary object and the FileSystemObject object. To use the Dictionary object, you create an object variable of type Dictionary, then set it to a new instance of a Dictionary object:

Dim dctDict As Scripting.Dictionary

Set dctDict = New Scripting.Dictionary

To use the other Scripting Runtime library objects in code, you must first create a variable of type FileSystemObject, then use the New keyword to create a new instance of the FileSystemObject, as shown in the following code fragment:

Dim fsoSysObj As Scripting.FileSystemObject

Set fsoSysObj = New Scripting.FileSystemObject

You can then use the variable that refers to the FileSystemObject to work with the Drive, Folder, File, and TextStream objects.

How do I work with files and folders by using the FileSystemObject?

Once you've created a new instance of the FileSystemObject, you can use it to work with drives, folders, and files in the file system.

The following procedure returns the files in a particular folder into a Dictionary object. The GetFiles procedure takes three arguments: the path to the directory, a Dictionary object, and an optional Boolean argument that specifies whether the procedure should be called recursively. It returns a Boolean value indicating whether the procedure was successful.

The procedure first uses the GetFolder method to return a reference to a Folder object. It then loops through the Files collection of that folder and adds the path and file name for each file to the Dictionary object. If the blnRecursive argument is set to True, the GetFiles procedure is called recursively to return the files in each subfolder.

Function GetFiles(strPath As String, _
                dctDict As Scripting.Dictionary, _
                Optional blnRecursive As Boolean) As Boolean
              
   ' This procedure returns all the files in a directory into
   ' a Dictionary object. If called recursively, it also returns
   ' all files in subfolders.
   
   Dim fsoSysObj      As Scripting.FileSystemObject
   Dim fdrFolder      As Scripting.Folder
   Dim fdrSubFolder   As Scripting.Folder
   Dim filFile        As Scripting.File
   
   ' Return new FileSystemObject.
   Set fsoSysObj = New Scripting.FileSystemObject
   
   On Error Resume Next
   ' Get folder.
   Set fdrFolder = fsoSysObj.GetFolder(strPath)
   If Err <> 0 Then
      ' Incorrect path.
      GetFiles = False
      GoTo GetFiles_End
   End If
   On Error GoTo 0
   
   ' Loop through Files collection, adding to dictionary.
   For Each filFile In fdrFolder.Files
      dctDict.Add filFile.path, filFile.path
   Next filFile

   ' If Recursive flag is true, call recursively.
   If blnRecursive Then
      For Each fdrSubFolder In fdrFolder.SubFolders
         GetFiles fdrSubFolder.path, dctDict, True
      Next fdrSubFolder
   End If

   ' Return True if no error occurred.
   GetFiles = True
   
GetFiles_End:
   Exit Function
End Function

   ' Return True if no error occurred.
   GetFiles = True
   
GetFiles_End:
   Exit Function
End Function

You can use the following procedure to test the GetFiles procedure. This procedure creates a new Dictionary object, passes it to the GetFiles procedure, then prints every file in the strDirPath directory and every file in any subdirectories to the immediate window.

Sub TestGetFiles()
   ' Call to test GetFiles function.

   Dim dctDict As Scripting.Dictionary
   Dim varItem As Variant
   Dim strDirPath As String
   
   strDirPath = "c:\my documents\"
   ' Create new dictionary.
   Set dctDict = New Scripting.Dictionary
   ' Call recursively, return files into Dictionary object.
   If GetFiles(strDirPath, dctDict, True) Then
      ' Print items in dictionary.
      For Each varItem In dctDict
         Debug.Print varItem
      Next
   End If
End Sub

You can try different values for the strDirPath argument to see how the procedure works using different directories.

How do I work with file attributes by using the FileSystemObject?

The File object and Folder object provide an Attributes property that you can use to read or set a file or folder's attributes, as shown in the following example.

The ChangeFileAttributes procedure takes four arguments: the path to a folder, an optional constant that specifies the attributes to set, an optional constant that specifies the attributes to remove, and an optional argument that specifies that the procedure should be called recursively.

If the folder path passed in is valid, the procedure returns a Folder object. It then checks to see whether the lngSetAttr argument was provided. If so, the procedure loops through all the files in the folder, appending the new attribute or attributes to each file's existing attributes. It does the same for the lngRemoveAttr argument—except, in this case, it removes the specified attributes if they exist for files in the collection.

Finally, the procedure checks whether the blnRecursive argument has been set to True. If so, it calls the procedure for each file in each subfolder of the strPath argument.

Function ChangeFileAttributes(strPath As String, _
                            Optional lngSetAttr As FileAttribute, _
                            Optional lngRemoveAttr As FileAttribute, _
                            Optional blnRecursive As Boolean) As Boolean
   
   ' This function takes a directory path, a value specifying file
   ' attributes to be set, a value specifying file attributes to be
   ' removed, and a flag that indicates whether it should be called
   ' recursively. It returns True unless an error occurs.
   
   Dim fsoSysObj      As Scripting.FileSystemObject
   Dim fdrFolder      As Scripting.Folder
   Dim fdrSubFolder   As Scripting.Folder
   Dim filFile        As Scripting.File
   
   ' Return new FileSystemObject.
   Set fsoSysObj = New Scripting.FileSystemObject
   
   On Error Resume Next
   ' Get folder.
   Set fdrFolder = fsoSysObj.GetFolder(strPath)
   If Err <> 0 Then
      ' Incorrect path.
      ChangeFileAttributes = False
      GoTo ChangeFileAttributes_End
   End If
   On Error GoTo 0
   
   ' If caller passed in attribute to set, set for all.
   If lngSetAttr Then
      For Each filFile In fdrFolder.Files
         If Not (filFile.Attributes And lngSetAttr) Then
            filFile.Attributes = filFile.Attributes Or lngSetAttr
         End If
      Next
   End If
   
   ' If caller passed in attribute to remove, remove for all.
   If lngRemoveAttr Then
      For Each filFile In fdrFolder.Files
         If (filFile.Attributes And lngRemoveAttr) Then
            filFile.Attributes = filFile.Attributes - lngRemoveAttr
         End If
      Next
   End If
   
   ' If caller has set blnRecursive argument to True, then call
   ' function recursively.
   If blnRecursive Then
      ' Loop through subfolders.
      For Each fdrSubFolder In fdrFolder.SubFolders
         ' Call function with subfolder path.
         ChangeFileAttributes fdrSubFolder.path, lngSetAttr, _
            lngRemoveAttr, True
      Next
   End If
   ChangeFileAttributes = True
  
ChangeFileAttributes_End:
   Exit Function
End Function

You can use the following procedure to test the ChangeFileAttributes procedure. In this example, all the files in the My Documents folder that have the hidden attribute set are made visible:

Sub TestChangeAttributes()
    If ChangeFileAttributes("c:\my documents", , _
        Hidden, False) = True Then
        MsgBox "File attributes succesfully changed!"
    End If
End Sub

You can try different values for the arguments in the ChangefileAttributes procedure to see how the procedure works.

Working with the FileSearch Object

The FileSearch object is a member of the Microsoft Office 9.0 Object Library. It exposes a programmatic interface to all the functionality of the Office File Open dialog box, including the features found in the Advanced Find dialog box, which is available from the Open dialog box. You can use the objects, methods, and properties of the FileSearch object to search for files or collections of files based on criteria you supply.

The following example shows how to use the FileSearch object to find one or more files of the type specified in the strFilespec argument. Notice that you can search for multiple file extensions by specifying those extensions as a semicolon-delimited list:

Function CustomFindFile(strFileSpec As String)
    ' This procedure demonstrates a simple file-search
    ' routine that displays a message box containing the
    ' names of all files in the "c:\" directory that
    ' match the file specifications provided in the
    ' strFileSpec argument.
    ' The strFileSpec argument can contain one or more file
    ' specifications in a semicolon-delimited list. For example, the
    ' following strFileSpec argument will return all files
    ' in the "c:\" that contain these extensions: "*.log;*.bat;*.ini"
    
    Dim fsoFileSearch   As Office.FileSearch
    Dim varFile         As Variant
    Dim strFileList     As String
    
    ' If the input in valid, then process the file search.
    If Len(strFileSpec) >= 3 And InStr(strFileSpec, "*.") > 0 Then
        Set fsoFileSearch = Application.FileSearch
        With fsoFileSearch
            .NewSearch
            .LookIn = "c:\"
            .FileName = strFileSpec
            .SearchSubFolders = False
            If .Execute() > 0 Then
                For Each varFile In .FoundFiles
                    strFileList = strFileList & varFile & vbCrLf
                Next varFile
            End If
        End With
        MsgBox strFileList
    Else
        MsgBox strFileSpec & " is not a valid file specification."
        Exit Function
    End If
End Function

The FileSearch object has two methods and several properties that you can use to build custom file-searching functionality into your custom Office solutions. The previous example uses the NewSearch method to clear any previous search criteria and the Execute method to carry out the search for the specified files. The Execute method returns the number of files found, and also supports optional parameters that let you specify the sort order, the sort type, and whether to use only saved Fast Find indexes to perform the search. You use the FoundFiles property to return a reference to the FoundFiles object that contains the names of all matching files found in your search.

You specify the directory to search by using the LookIn property, and you use the SearchSubFolders property to specify whether the search should extend to subfolders of the directory specified in the LookIn property. The FileName property supports wildcard characters and a semicolon-delimited list of file names or file-type specifications.

Where to Get More Info

The techniques and technologies discussed here should give you lots of ideas for working with the file system regardless of the tools you have available to you. For additional information, check out the following resources:

• For more information on working with VBA in Office solutions, see https://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deconGettingMostOutOfVisualBasicForApplications.asp.
• For more information on using FileSystemObject, see HOWTO: Use FileSystemObject with Visual Basic (https://support.microsoft.com/support/kb/articles/Q186/1/18.ASP).
• For more information on the advanced features of the FileSearch object, see Using Advanced File-Searching Features.
• Check in regularly at the Office Developer Center (https://msdn.microsoft.com/office) for information and technical articles on Office solution development.

David Shank is a programmer/writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native Northwesterners who still lives in the Northwest.