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.
Determine If a File Exists in a Directory
'------------------ Modules ------------------ 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:
'------------------ Modules ------------------ Sub TestFileExists() If FileExists("C:\myfile.txt") = True Then MsgBox "File exists." Else MsgBox "File does not exist." End If End Sub
The following code illustrates how to use the FileSystemObject object to determine if a file or folder exists in a directory.
'------------------ Modules ------------------ Sub FileExist() Dim objFso As Object Dim strPath As String strPath = ThisWorkbook.path & Application.PathSeparator Set objFso = CreateObject("Scripting.FileSystemObject") Debug.Print objFso.FolderExists(strPath & "TestFolder") Debug.Print objFso.FileExists(strPath & "TestFile.txt") End Sub
Most VBA code should be placed in Standard Modules unless specified.
If you see a comment
'------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?
The following steps teach you how to put VBA code into a Standard Module:
- Activate the Visual Basic Editor by pressing ALT + F11.
- Right-click the project/workbook name in the Project Window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
- Click Run button on the Visual Basic Editor toolbar.
- For more information, learn this course: Programming with Excel VBA