An Excel Macro to Determine Whether a Workbook Is Open

By Michael Alexander

As you think about automatically opening Excel workbooks, 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 the message shown warning that any unsaved changes will be lost. You can protect against such an occurrence by checking whether a given file is already open before trying to open it again.

Avoid this warning message.

Avoid this warning message.

How the macro 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 you to pass any filename to it to test whether that file is already open.

The gist of this code is simple. You’re 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 you try to assign a closed workbook to the variable, an error occurs.

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 your 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, workbook is already open
    If Err.Number = 0 Then
    FileIsOpenTest = True
    Else
    FileIsOpenTest = False
    End If
End Function

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 your String variable.

In Step 2, you tell Excel that there may be an error running this code and, in the event of an error, resume the code. Without this line, the code would simply stop when an error occurs. Again, you test a given filename to see if it can be assigned to an Object variable. If the given workbook can be assigned, it’s open; if an error occurs, it’s closed.

In Step 3, you attempt to assign the given workbook to the TestBook Object variable. The workbook you try to assign is 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 you to pass it as a variable instead.

In Step 4, you simply check to see if an error occurred. If an error did not occur, the workbook is open, so you set the FileIsOpenTest to True. If an error occurred, the workbook is not open and you set the FileIsOpenTest to False.

Again, this function can be used to evaluate any file you pass to it, via its TargetWorkbook argument. This is the beauty of putting the macro into a function.

The following macro demonstrates how to implement this function. Here, you call the new FileIsOpenTest function to make sure that the user cannot open an already opened file:

Sub Macro1()
'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))