Use the VBA Dir function to check if a file exists. The VBA Dir function returns the name of a valid file, so you can use it to test whether a file exists. When the VBA Dir function returns an empty string, it means the file does not exist.

The Dir function can do a lot more than just tell you whether or not a file exists. It can be used to analyze folders and check file properties, as well. Today, however, I’m going to introduce you to an easy-to-remember user defined function to test if a file exists.

This function I created, named FileExists, is a boolean so it will return True if the file exists and False if the file doesn’t exist. All you have to do is copy and paste the function to a module and pass it a string with the file path you want to check.


VBA Check if File Exists

Function FileExists(FilePath As String) As Boolean
Dim TestStr As String
    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

How to use the FileExists UDF

Once you’ve copied and pasted the above macro into a module in your VBA editor, you can begin using the function. The function only accepts 1 argument, so it’s simple to use!

Just pass it a path name or a variable containing a path name to see it work. Here’s an example:

Sub FileExistsDemo()
'VBA Check if File Exists
Dim strFile As String
strFile = "C:\Users\Ryan\Documents\DataFile.txt"

If FileExists(strFile) Then
    'File Exists
Else
    'File Does Not Exist
End If
End Sub

That’s pretty easy to remember, right?


More about the VBA Dir Function

This isn’t the first time you’ve seen me use the Dir function. I’ve used it in the past to count files in a folder and loop through files in a folder. It’s quite versatile!

The VBA Dir function is pretty smart, too. It can accept wildcards, like the asterisk (*) and question mark (?).

  • Asterisk (*) - Used to search multiple unknown characters. For example, “a*.txt” will cause Dir to search for a file of any length beginning with an “a” and ending with a “.txt”
  • Question Mark (?) - Used to search individual unknown characters. For example, “B?.txt” will cause Dir to search for a file with a 2 letter prefix beginning with a B, like “B1.txt” or “Bc.txt”

If wildcards are used, the Dir function will return the name of the FIRST file it finds meeting the criteria. My FileExists function will return True if any file is found meeting the wildcard conditions.

Here’s a quick demo showing how to use my FileExists Function with a wildcard to see if a file exists.

Sub FileExistsWildCardDemo()
'VBA Check if File Exists
Dim strFile As String
strFile = "C:\Users\Ryan\Documents\A*.txt"
If FileExists(strFile) Then
    'File beginning with A and ending with .txt exists
Else
    'File beginning with A and ending with .txt exists does not Exist
End If
End Sub

VBA Dir Second Argument

Believe it or not, the Dir function can do even more! It accepts an optional second argument so you can restrict your search to files meeting certain attribute parameters. I don’t find myself having to use these arguments that often, but here they are if you want to know:

  • vbNormal (default)
  • vbReadOnly
  • vbHidden
  • vbSystem
  • vbVolume
  • vbDirectory
  • vbAlias

Mac Users and the Dir Function

I’m going to warn you right now that wildcards only work with the Dir function on a Windows operating system. Since asterisks and question marks are valid file name characters for Mac users, you can not pass wildcards to the VBA Dir function on a Mac.

Instead, Mac users can use the optional second argument to pass the Dir function a MacID defining what file type to search for. For example, to search for text files, you can use something like:

If Dir(Path, MacID("TEXT")) Then
    'text file in the Path folder was found
Else
    'text file in the Path folder was not found
End If

I don’t own a Mac, so I can’t test it but I believe the MacID function must accept a string that’s four characters long. With that said, I don’t know what the MacID of files like PDFs are if you need to search for a PDF with a certain name in a folder. Leave a comment if you know the answer!

The second limitation of MacIDs is they only exist for files created on the Mac. If you’re accessing a server with files created by Macs and PCs, the PC files will not have MacIDs.


I hope you enjoyed this little tutorial. I have more grab-and-go macro examples in my VBA Code Library. Grab what you need!

Please subscribe to my free wellsrPRO VBA Training Program. Once you subscribe, you’ll get access to a free copy of my Excel Add-In with tons of useful features and you’ll get monthly tutorials where I show you how to do some pretty cool stuff with VBA:)

Share this article with the world on Google+, Twitter and Facebook!


About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.