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
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.
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
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)
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:)
About The VBA Tutorials Blog
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.Follow