Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Syntax
Dir [ (pathname, [ attributes ] ) ]
The Dir function syntax has these parts:
| Part | Description |
|---|---|
| pathname | Optional. String expression that specifies a file name; may include directory or folder, and drive. A zero-length string ("") is returned if pathname is not found. |
| attributes | Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes. |
Settings
The attributes argument settings are:
| Constant | Value | Description |
|---|---|---|
| vbNormal | 0 | (Default) Specifies files with no attributes. |
| vbReadOnly | 1 | Specifies read-only files in addition to files with no attributes. |
| vbHidden | 2 | Specifies hidden files in addition to files with no attributes. |
| vbSystem | 4 | Specifies system files in addition to files with no attributes. Not available on the Macintosh. |
| vbVolume | 8 | Specifies volume label; if any other attribute is specified, vbVolume is ignored. Not available on the Macintosh. |
| vbDirectory | 16 | Specifies directories or folders in addition to files with no attributes. |
| vbAlias | 64 | Specified file name is an alias. Available only on the Macintosh. |
These constants are specified by Visual Basic for Applications and can be used anywhere in your code in place of the actual values.
Remarks
In Microsoft Windows and macOS, Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files.
Because the Macintosh doesn't support the wildcards, use the file type to identify groups of files. Use the MacID function to specify file type instead of using the file names. For example, the following statement returns the name of the first TEXT file in the current folder:
Dir("SomePath", MacID("TEXT"))To iterate over all files in a folder, specify an empty string:
Dir("")If you use the MacID function with Dir in Microsoft Windows, an error occurs.
Any attribute value greater than 256 is considered a MacID value.
You must specify pathname the first time you call the Dir function, or an error occurs. If you also specify file attributes, pathname must be included.
Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (""). After a zero-length string is returned, you must specify pathname in subsequent calls, or an error occurs.
You can change to a new pathname without retrieving all of the file names that match the current pathname. However, you can't call the Dir function recursively. Calling Dir with the vbDirectory attribute does not continually return subdirectories.
Because file names are retrieved in case-insensitive order on Windows and case-sensitive order on macOS, you may want to store returned file names in an array, and then sort the array.
Examples
The following code illustrates how to use the Dir function to determine if a file exists in a directory.
'------------------ Modules ------------------
Sub TestFile()
Dim fil As String
fil = ThisWorkbook.path & "\Products.xlsx"
If Len(Dir(fil)) > 0 Then
MsgBox "Workbook exists!"
Else
MsgBox "Workbook does not exist."
End If
End Sub