How to use Dir function in VBA?

The Dir function in VBA (Visual Basic for Applications) is used to return the name of a file, directory, or folder that matches a specified pattern or attributes. It is often used to loop through files or folders in a directory. The function can return one file name at a time, and it must be called repeatedly to get additional file names. Here’s the syntax for the Dir function:

VBA


Dir([pathname], [attributes])

pathname: This is an optional string argument that specifies the file name or a directory or folder, and it can include wildcard characters (* and ?) for file name matching.
attributes: This is an optional argument that specifies file attributes to search for, such as vbReadOnly, vbHidden, vbSystem, vbDirectory, vbArchive, or vbNormal, as well as combinations using the OR operator.

Here’s an example of how you can use the Dir function in VBA to loop through all the .xlsx files in a directory:

VBA


Sub ListExcelFiles()
    Dim folderPath As String
    Dim fileName As String
    
    ' Specify the folder path
    folderPath = "C:Files"
    
    ' Ensure folder path ends with a backslash
    If Right(folderPath, 1) <> "" Then folderPath = folderPath & ""

    ' Get the first Excel file (.xlsx) in the specified folder
    fileName = Dir(folderPath & "*.xlsx")
    
    ' Loop through each Excel file in the directory
    Do While fileName <> ""
        ' Output the file name to the immediate window (Debug.Print)
        Debug.Print fileName
        
        ' Get the next file name
        fileName = Dir() ' No arguments needed after the first call
    Loop
End Sub

In the above example, the Dir function is first called with the pathname argument that includes a wildcard to match all .xlsx files within the given folder. The result is stored in the fileName variable. A loop is then initiated to continue calling Dir without any arguments. Each subsequent call to Dir returns the next filename matching the pattern specified in the first call until there are no more files to return, at which point it returns an empty string (“”) and the loop terminates. Remember to handle cases where the attributes argument might be relevant, especially when looking for hidden or system files. You can specify multiple attributes by using the OR operator, like so:

VBA


fileName = Dir(folderPath & "*.*", vbHidden Or vbSystem)

Always be cautious with loops like this, because if there are no files matching the pattern, the loop will never start, and if for some reason Dir stops returning new names (e.g., due to an error), your loop might become infinite. Always include error handling and exit strategies where appropriate.

facebooklinkedin