Excel Macro: Determine If a File Exists in a Directory

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.

1. 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.
2. 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.
3. 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

How to Use This Macro