VBA DIR: Check If File or Folder Exists

What Does VBA Dir Function Do?

The VBA DIR function is a powerful tool in Excel. It helps you find files or folders that match a specific name or pattern.

Here are some use cases for VBA DIR:

• Find Files or Folders: Use DIR to search for files or folders. For example, you can find all Excel files in a folder.
• Check if a File Exists: Provide the name of a file. If it exists, DIR returns the name. If not, it returns an empty string.
• List Files: Use DIR in a loop to get a list of all matching files or folders.

Syntax of VBA DIR Function

DIR [( path [, attributes ])]

• Path: The name or pattern of the file(s) you’re looking for. If the name is not found, DIR returns an empty string
• Attributes (Optional): The available attributes are listed in the table below:

💡 The default is vbNormal, which are files with no specific attributes. 

Using Wildcards with DIR

Wildcards are symbols that represent any character(s) and are very useful for pattern matching.

Types of Wildcards

Asterisk (*)

Represents any number of characters.

Examples:

• Exc* – Matches any text starting with “Exc”
• el – Matches any text ending with “el” Excel – Matches any text starting with “Exc”, ending with “el”, and anything in between

Question Mark (?)

Represents a single character.

Examples:

• ??cel – The first two characters can be anything, but the last three must be “cel”
• Ex?el – The first two characters must be “Ex”, the fourth and fifth must be “el”, but the third can be anything

Featured Course

Unlock Excel VBA & Excel Macros

Automate ANYTHING you need done in Excel with VBA and macros. Go from Beginner to VBA Expert and design automations with confidence.

Learn More

Example 1: Check if a File Exists

We will use the VBA DIR function to check if a file exists. If it doesn’t, we will show a message. If it does, we will open the file.

Version 1: Basic Check

• Open the Visual Basic Editor: Press ALT + F11 and create a new module (i.e. “LessonsFilesFolders”).

• Declare Variables: We declare a variable named FileName to hold the returned value.

Dim FileName As String

• Check for File: The next step is to query a folder for a file and return the filename if it exists, or an empty string if the file does not exist.  We will store the response in the FileName variable we created in the previous step.

FileName = VBA.FileSystem.Dir(“your folder name\your file name”)

• In our example we will use the following code:

FileName = VBA.FileSystem.Dir(“C:\Users\LG\Desktop\VBA\S2_recordMacros_start.xlsx”)

• If the file does not exist, the DIR function will return an empty string.  We will test for the empty string response with an IF statement. If the file does not exist, we will display a message stating such.  If the file does exist, this first version will simply show the filename in a message box.

If FileName = VBA.Constants.vbNullString Then
    MsgBox "File does not exist."
Else
    MsgBox FileName
End If

• The completed code should look like the following:

Sub FileExists()
Dim FileName As String
    FileName = VBA.FileSystem.Dir("C:\Users\LG\Desktop\VBA\S2_recordMacros_start.xlsx")
    If FileName = VBA.Constants.vbNullString Then
        MsgBox "File does not exist."
    Else
        MsgBox FileName
    End If
    
End Sub

• Run the code by pressing F5 and observe the response.

This confirms that the file exists in the defined folder.

Version 2: Using Wildcards

• Modify the Check

FileName = VBA.FileSystem.Dir("C:\Users\LG\Desktop\VBA\S2_*start.xls?)

• We will also alter the code; instead of displaying a message, we will open the requested file.

Workbooks.Open "C:\Users\LG\Desktop\VBA\" & FileName

• The updated code should appear as follows:

Sub FileExists()
Dim FileName As String
    FileName = VBA.FileSystem.Dir("C:\Users\LG\Desktop\VBA\S2_*start.xls?")
    If FileName = VBA.Constants.vbNullString Then
        MsgBox "File does not exist."
    Else
        Workbooks.Open "C:\Users\LG\Desktop\VBA\" & FileName
    End If
    
End Sub

• Run the code by pressing F5 and observe that the file opens.

Example 2: Check If a Folder Exists

We will check if a folder exists. If it doesn’t, we will ask the user if they want to create it.

Declare Variables:

We will create two variables:

• Path – Hold the full folder\filename information
• Folder – Hold only the folder name

Dim Path as String
Dim Folder as String

Set the Path:

• We will set the Path variable to point to a folder that does not exist:

Path = “C:\Users\LG\Desktop\VBA\S12”

• We will set the Folder variable to hold the folder location stored by the Path variable.  Because this is a folder, we will use the optional constant vbDirectory in the DIR function.

Folder = Dir(Path,vbDirectory)

• As we did earlier, we will check to see if the response returns an empty string.  If the Folder variable contains an empty string, we will prompt the user to ask if they wish to create the folder.

• We need to store the user’s response, so we will create a variable to hold the response.

Dim Answer as VbMsgBoxResult

Check for Folder

• If the folder does not exist, we will display a message and store the user’s response in the Answer variable.

Answer = MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?")

• Now we will test the answer.  We will use a Case statement to test the response.
  • If the user responds with “Yes”, we will create the folder.  If the user responds with anything else, we will exit the subroutine.

Select Case Answer
    Case vbYes
        VBA.FileSystem.MkDir (Path)
    Case Else
        Exit Sub
End Select

• If the folder does exist, we will inform the user of its existence with a message box response.

Else
    MsgBox "Folder exists."

• The completed code should look like the following:

Sub Path_Exists()
Dim Path As String
Dim Folder As String
Dim Answer As VbMsgBoxResult
    Path = "C:\Users\LG\Desktop\VBA\S12"
    Folder = Dir(Path, vbDirectory)
 
    If Folder = vbNullString Then
        Answer = MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?")
        Select Case Answer
            Case vbYes
                VBA.FileSystem.MkDir (Path)
            Case Else
                Exit Sub
        End Select
    Else
        MsgBox "Folder exists."
    End If
End Sub

• Run the code by pressing F5. Because the folder does not exist, we are presented with the following message prompt.

• If we answer “Yes”, the folder is created.

• If we execute the macro a second time, we see the following response.

• This is because the folder was created in the previous test.

Conclusion

We have demonstrated how you can use the DIR function in Excel VBA. It lets you test if a file or folder exists. You can then decide what to do based on the result.

Practice Workbook

Feel free to Download the Workbook HERE.

LinkedIn Twitter YouTube

Leila Gharani

I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.

As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.

I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.

More About Leila Join 400,000+ professionals in our courses