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
Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
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 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)
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.