VBA Dir function

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

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).

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 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"

Simply the best place to learn VBA!