An Excel Macro to Determine Whether a Workbook Exists - dummies

An Excel Macro to Determine Whether a Workbook Exists

By Michael Alexander

This Excel macro allows you to pass a file path to evaluate whether the file is there. For example, you may have a process that manipulates a file somewhere on your PC. For example, you may need to open an existing workbook to add data to it on a daily basis. In this case, you may need to test to see whether the file you need to manipulate exists.

How the macro works

The first thing to notice about this macro is that it is a function, not a Sub procedure. Making this macro a function enables you to pass any file path to it.

In this macro, you use the Dir function, which returns a string that represents the name of the file that matches what you pass to it. This function can be used in lots of ways, but here, you use it to check whether the file path you pass to it exists:

Function FileExists(FPath As String) As Boolean
'Step 1: Declare your variables
    Dim FName As String
'Step 2: Use the Dir function to get the filename
    FName = Dir(FPath)
'Step 3:  If file exists, return True; else False
    If FName <> " Then FileExists = True _
    Else: FileExists = False
End Function

Step 1 declares a String variable that holds the filename that returns from the Dir function. FName is the name of the String variable.

In Step 2, you attempt to set the FName variable. You do this by passing the FPath variable to the Dir function. This FPath variable is passed via the function declarations (see the first line of the code). This structure prevents you from having to hard-code a file path, passing it as a variable instead.

If the FName variable can’t be set, the path you passed does not exist. Thus, the FName variable is empty. Step 3 merely translates that result to a True or False expression.

Again, this function can be used to evaluate any file path you pass to it. This is the beauty of writing the macro as a function.

The following macro demonstrates how to use this function:

Sub Macro1()
    If FileExists("C:TempMyNewBook.xlsx") = True Then
        MsgBox "File exists."
        MsgBox "File does not exist."
    End If
End Sub

How to use the macro

To implement this macro, you can copy and paste both pieces of code into a standard module:

  1. Activate Visual Basic Editor by pressing Alt+F11.

  2. Right-click the project/workbook name in the project window.

  3. Choose Insert→Module.

  4. Type or paste the code in the newly created module.