Dir Function Description
The VBA Dir function returns the first filename that matches the path and, if needed, the attributes specified. To retrieve the subsequent paths/filenames that match the provided path and attributes you need to call Dir again with no arguments.
The VBA Dir function is especially useful for listing all files located in a certain directory or checking whether files exist. The function can well be used to traverse directories (scroll down for more information).
VBA Dir Syntax
Syntax for the Dir function in VBA:
Dir [( path [, attributes ] ) ]
Parameters
path
Optional. Path to a file, folder, or directory. If path is not found, the Dir function will return a zero-length string (vbNullString).
attributes
Optional. Logical and of the file attributes. These can be one or a combination of the following values:
| Constant | Value | Description |
|---|---|---|
| vbNormal | 0 | Normal (Default) |
| vbReadOnly | 1 | Read-only |
| vbHidden | 2 | Hidden |
| vbSystem | 4 | System file |
| vbVolume | 8 | Volume label |
| vbDirectory | 16 | Directory or folder |
| vbAlias | 64 | File name is an alias |
Other notes
You can use wildcard characters to specify multiple files. The patterns that you can choose from are:
| Wildcard | Description |
|---|---|
| * | Allows you to match any string of any length (including zero length) |
| ? | Allows you to match on a single character |
Example usage
Below are some common VBA Dir function VBA examples.
Checking whether a file exists:
Dir "C:\myfile.txt" 'Result: "myfile.txt" Dir "C:\this_file_does_not_exist.txt" 'Result: ""
Listing files using wildcard:
Dir "C:\*World.ppt" 'Result: "Hello_World.ppt", "World.ppt" etc. Dir "C:\?orld.ppt" 'Result: "World.ppt", "Lorld.ppt", "zorld.ppt", "1orld.ppt"
