Macro 9: Determine Whether a Workbook Exists in a Directory
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 new data to it on a daily basis. In these cases, you may need to
test to see whether the file you need to manipulate actually exists. This macro allows you to pass a
file path to evaluate whether the file is there.
How it 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 us to pass any file path to it.
In this macro, we use the Dir function. The Dir function 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, we are
using it to check if the file path we 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 file name
FName = Dir(FPath)
‘Step 3: If file exists, return True else False
If FName <> “” Then FileExists = True _
Else: FileExists = False
End Function
1. Step 1 declares a string variable that holds the filename that returns from the Dir function.
FName is the name of the string variable.
2. In Step 2, we attempt to set the FName variable. We 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 us from having to hard-code a file path, passing it as
a variable instead.
3. If the FName variable can’t be set, this means the path we passed does not exist. Thus
the FName variable is empty. Step 3 merely translates that result to a True or False
expression.
Again, this is a function that can be used to evaluate any file path you pass to it. That’s the
beauty of writing this macro as a function.
The following macro demonstrates how to use this function:
Sub Macro9)
If FileExists(“C:\Temp\MyNewBook.xlsx”) = True Then
MsgBox “File exists.”
Else
MsgBox “File does not exist.”
End If
End Sub
How to use it
To implement this macro, you can copy and paste both pieces of code into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
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.
