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

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

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 name 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!

For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.

After you subscribe, share what you’re automating on Twitter and Facebook.