Learn how to use the Excel DIR function with syntax and examples.
The Microsoft Excel 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 Microsoft Excel 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:
|vbDirectory||16||Directory or folder|
|vbAlias||64||File name is an alias|
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|
The DIR function can be used in the following versions of Microsoft Excel:
The DIR function can be used in Microsoft Excel as the following type of function:
The DIR function can only be used in VBA code in Microsoft Excel.
Let's look at some Excel DIR function examples and explore how to use the DIR function in Excel VBA code:
Dir("C:\instructions.doc") Result: "instructions.doc" Dir("C:\in*.doc") Result: "instructions.doc" Dir("C:\instruction?.doc") Result: "instructions.doc"
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.
Question: How can I use the DIR function to test whether a file exists?
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:\TOTN\Excel\Examples", vbDirectory)) = 0 Then MkDir "c:\TOTN\Excel\Examples" End If
In this example, the code would first check to see if the c:\TOTN\Excel\Examples directory exists. If it doesn't exist, the MKDIR statement would create a new directory called Examples under the c:\TOTN\Excel directory.
(scroll to see more)