Sometimes you may need to determine if a file exists in a directory. For instance, the purpose of your macro may be to open and read from a text file. Before doing so, you will want to check to see if the file exists, in order to avoid an error. The following macro code allows you to pass a file path to evaluate if the file is there.
Function FileExists(FilePath As String) As Boolean 'Step 1: Declare your variables. Dim FileName As String 'Step 2: Use the Dir function to get the file name FileName = Dir(FilePath) 'Step 3: If file exists, return True else False If FileName <> "" Then FileExists = True _ Else: FileExists = False End Function
How This Macro Works
The first thing to notice about this macro FileExists is a function, not a sub procedure. As you will see, making this macro a function enables us to pass any file path to it to test whether that file is exist in a directory.
This function works by using the Dir function, which checks for a file in a directory. The Dir function returns a string that represents the name of the file that matches what you pass to it. If the file exists, then Dir returns the full path of the file. The True/False condition of the function is derived by comparing what Dir returns against an empty string. If something is returned, the file exists because Dir doesn't return an empty string.
- Step 1 declares a string variable that holds the file name that returns from the Dir function. FileName is the name of the string variable.
- In Step 2, we attempt to set the FileName variable. We do this by passing the FilePath variable to the Dir function. This FilePath variable is passed via the function declarations (see the first line of the code). This structure prevents us from having to hard-code a file path, passing it as a variable instead.
- If the FilePath variable can’t be set, this means the path we passed does not exist. Thus the FilePath variable is empty. Step 3 merely translates that result to a True or False expression.
The following macro demonstrates how to use this function:
Sub TestFileExists() If FileExists("C:\myfile.txt") = True Then MsgBox "File exists." Else MsgBox "File does not exist." End If End Sub
How to Use This Macro
To implement this macro, you need to copy and paste it into the standard module:
- Activate the Visual Basic Editor by pressing
- In the Project window, find your project/workbook name and Right-click the project/workbook name in the Project window.
- Choose Insert ➜ Module.
- Type or paste the code in the newly created module.