The VBA Dir Function

Description

The VBA Dir function returns the first file or directory name that matches a specified pattern and attributes.

If the Dir function is then called a second time, with no arguments, it remembers the arguments from the previous call and returns the second matching file or directory.

If there are no matching files, the Dir function returns an empty string.

The syntax of the VBA Dir function is:

Dir( Path, [Attributes] )

Where the function arguments are:

Path

The partial or full path and/or filename that you want to search for.

[Attributes]

An optional vbFileAttribute enumeration value (or combination of values), specifying the attributes of any matching files.

This can be one or more of the following values:

vbNormal	-	files with no specific attributes
vbReadOnly	-	read only files, in addition to files with no attributes
vbHidden	-	hidden files, in addition to files with no attributes
vbSystem	-	system files, in addition to files with no attributes (not available on the Macintosh)
vbVolume	-	volume label
vbDirectory	-	directories or folders, in addition to files with no attributes
vbAlias	-	specified filename is an alias (only available on the Macintosh)

If omitted, the [Attributes] argument is set to vbNormal.

Note that if more than one of the above values is used, these must be separated by the + symbol (e.g. vbHidden + vbSystem).

Wildcards

If you are working on Microsoft Windows, you can use the following wildcards within the Path argument:

? - matches any single character
* - matches any sequence of characters

Note that these characters do not work as wildcards on the Macintosh.

VBA Dir Function Examples

Example 1 - Search for a File

' Find the file "C:\Documents\data.txt"

								Dim fName As String

								fName = Dir( "C:\Documents\data.txt" )

								
									' The variable "fName" now contains the string "data.txt" (if the file

									' is found) or an empty text string otherwise.

If fName = "" Then

' File not found!

Else

' File found. Open file to extract data.

End If

The above call to the VBA Dir function returns the file name "data.txt" if this file is found in the directory "C:\Documents" or returns an empty string if the file is not found.

Example 2 - List All Files and Directories

The following VBA code uses the Dir function to list all the files and directories within "C:\".

' List the files and directories within "C:\".

								Dim fList As String

								Dim fName As String

								fName = Dir( "C:\", vbDirectory )

									' The variable fName now contains the name of the first file or directory within "C:\".

Do While fName <> ""
  							
  										' Store the current file or directory name in the string fList.

  									fList = fList & vbNewLine & fName
  								
  									' Get the next file or directory within "C:\".

  								fName = Dir()

  									' The variable fName now contains the name of the next file or directory within "C:\".
  								
Loop

' Display the list of directories in a message box.
MsgBox( "File List:" & fList )

The above VBA code causes the following message box to be displayed, listing the names of all the files and directories in "C:\".

VBA Message Box Showing List of Files and Directories

Note that the initial call to the Dir function provides the Path and [Attributes] arguments, and these values are remembered in the subsequent calls to the Dir function.

Example 3 - List All .CSV Files in a Directory

The following VBA code uses the Dir function to list all .csv files in the directory C:\DataFiles\.

Note: this example uses the wildcard *, which only works on computers running Microsoft Windows.

' Find all .csv files in directory "C:\DataFiles\"

								Dim fList As String

								Dim fName As String

								fName = Dir( "C:\DataFiles\*.csv" )

									' The variable fName now contains the name of the first .csv file within "C:\DataFiles\".

Do While fName <> ""
  							
  										' Store the current file in the string fList.

  									fList = fList & vbNewLine & fName
  								
  									' Get the next .csv file within "C:\DataFiles\".

  								fName = Dir()

  									' The variable fName now contains the name of the next .csv file within "C:\DataFiles\".
  								
Loop

' Display the list of .csv files in a message box.
MsgBox( "List of .csv Files:" & fList )

The above VBA code causes the following message box to be displayed, listing the names of all the .csv files in the directory "C:\DataFiles\".

VBA Message Box Showing List of .csv Files

Note that, as in the previous example, the Path argument is provided to the initial call to the Dir function, and this value is remembered in the subsequent calls to the function.

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page