Macro 8: Determine Whether a Workbook Is Already Open
The previous macro automatically opened a workbook based on the user’s selection. As we think
about automatically opening workbooks, you must consider what may happen if you attempt to
open a book that is already open. In the non-VBA world, Excel attempts to open the file again, with a
warning that any unsaved changes will be lost. In VBA, it’s a good idea to protect against such an
occurrence by checking if a given file is already open before trying to open it again.
How it works
The first thing to notice about this macro is that it is a function, not a sub procedure. As you will
see, making this macro a function enables us to pass any filename to it to test whether that file is
already open.
The gist of this code is simple. We are testing a given filename to see if it can be assigned to an object
variable. Only opened workbooks can be assigned to an object variable. When we try to assign a
closed workbook to the variable, an error occurs.
So if the given workbook can be assigned, the workbook is open; if an error occurs, the workbook is
closed.
Function FileIsOpenTest(TargetWorkbook As String) As Boolean
‘Step 1: Declare variables
Dim TestBook As Workbook
‘Step 2: Tell Excel to Resume on Error
On Error Resume Next
‘Step 3: Try to assign the target workbook to TestBook
Set TestBook = Workbooks(TargetWorkbook)
‘Step 4: If no error occurred then Workbook is already
open If Err.Number = 0 Then
FileIsOpenTest = True
Else
FileIsOpenTest = False
End If
End Function
1. The first thing the macro does is to declare a string variable that will hold the filename that
the user chooses. TestBook is the name of our string variable.
2. In Step 2, we are telling Excel that there may be an error running this code. In the event of an
error, resume the code. Without this line, the code would simply stop when an error occurs.
Again, we are testing a given filename to see if it can be assigned to an object variable. So if
the given workbook can be assigned, it’s open; if an error occurs, it’s closed. We need to have
the code continue if an error occurs.
3. In Step 3, we are attempting to assign the given workbook to the TestBook object variable.
The workbook we are trying to assign is itself a string variable called TargetWorkbook.
TargetWorkbook is passed to the function in the function declarations (see the first line of
the code). This structure eliminates the need to hard-code a workbook name, allowing us to
pass it as a variable instead.
4. In Step 4, we simply check to see if an error occurred. If an error did not occur, the workbook
is open, so we set the FileIsOpenTest to True. If an error occurred, that means the
workbook is not open. In that case, we set the FileIsOpenTest to False.
TargetWorkbook argument. That is the beauty of putting this macro into a function.
The following macro demonstrates how to implement this function. Here, we are using the
same macro you saw in the previous section, “Macro 7: Opening a Specific Workbook
Defined by the User,” but this time, we are calling the new FileIsOpenTest function to
make sure the user cannot open an already open file.
Sub Macro8()
‘Step 1: Define a string variable.
Dim FName As Variant
Dim FNFileOnly As String
‘Step 2: GetOpenFilename Method activates dialog box.
FName = Application.GetOpenFilename( _
FileFilter:=”Excel Workbooks,*.xl*”, _
Title:=”Choose a Workbook to Open”, _
MultiSelect:=False)
‘Step 3: Open the chosen file if not already opened.
If FName <> False Then
FNFileOnly = StrReverse(Left(StrReverse(FName), _
InStr(StrReverse(FName), “\”) – 1))
If FileIsOpenTest(FNFileOnly) = True Then
MsgBox “The given file is already open”
Else
Workbooks.Open Filename:=FName
End If
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.
5. Optionally, you can assign the macro to a button (see the section on “Assigning a macro to a
button and other form controls” in Part I).
