Excel – Dir Function (VBA only)

In Excel, the Dir function returns the first filename that matches the pathname and attributes specified. To retrieve additional filenames that match pathname and attributes, call Dir again with no arguments.

The syntax for the Dir function is:

Dir [( path [, attributes ] ) ]

path is optional. It the path to a file, folder, or directory. If the path is not found, the Dir function will return a zero-length string.

attributes is optional. It is the sum of the file attributes. File attributes can be one or a combination of the following values:

Note:

You can use wildcard characters to specify multiple files. The patterns that you can choose from are:

* allows you to match any string of any length (including zero length)
? allows you to match on a single character

If you wish to iterate over all files in a directory, use an empty string:

Dir ("")

Applies To

Excel 2007, Excel 2003, Excel XP, Excel 2000

VBA Code

The Dir function can only be used in VBA code. For example:

Dim LResult As String

LResult = Dir("C:instructions.doc")

In this example, the variable called LResult would now contain the filename of the instructions.doc file.

Frequently Asked Questions

Question: How can I use the Dir function to test whether a file exists?

Answer: This can be done with a formula that utilizes a combination of the Dir function, IF function, and Len function.

For example:

If Len(Dir("c:Instructions.doc")) = 0 Then
    Msgbox "This file does NOT exist."
Else
    Msgbox "This file does exist."
End If

Question: I'm not sure if a particular directory exists already. If it doesn't exist, I'd like to create it using VBA code. How can I do this?

Answer: You can test to see if a directory exists using the VBA code below:

If Len(Dir("c:TOTNExcelExamples", vbDirectory)) = 0 Then
    MkDir "c:TOTNExcelExamples"
End If

In this example, the code would first check to see if the c:TOTNExcelExamples directory exists. If it doesn't exist, the MkDir statement would create a new directory called Examples under the c:TOTNExcel directory.

Excel – Dir Function (VBA only)

SOURCE

LINK

LANGUAGE

ENGLISH