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
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
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!
Take a break from your research and check out my Excel Add-ins page for some great products. This is my primary source of blog revenue and the money I receive from sales helps pay to keep this website online and accessible for all to learn. hint hint :)
To get your VBA questions answered, reach out to me via my VBA Consulting page.