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.

Macro Code

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:

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:

  1. Activate the Visual Basic Editor by pressing ALT+F11.
  2. In the Project window, find your project/workbook name and Right-click the project/workbook name in the Project window.
  3. Choose InsertModule.
    Insert Module
  4. Type or paste the code in the newly created module.
    Type or Paste Macros Code in a Module

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>