Excel Macro: Determine If a Excel Workbook is Already Open

The last macro automatically opened a specific workbook chosen by the user's selection. As we consider automatically opening workbooks, you must think about what may happen if you attempt to open a workbook that is already open. In the non-VBA world, Excel attempts to open the workbook again, with a warning that any unsaved changes will be lost. In VBA, it's a great idea to protect against such an occurrence by checking if a given file is already open before trying to open it again. The function below can be used to determine if a excel workbook is already open or not.

Determine If a Excel Workbook is Already Open

'------------------ Modules ------------------
Function WorkbookOpenTest(TargetWorkbook As String) As Boolean
'Step 1:  Declare variables
    Dim TestWorkbook 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 TestWorkbook = Workbooks(TargetWorkbook)

'Step 4: If no error occurred then Workbook is already open
    If Err.Number = 0 Then
        WorkbookOpenTest = True
    Else
        WorkbookOpenTest = False
    End If
End Function

How This Macro Works

The first thing to notice about this macro WorkbookOpenTest 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.

1. The first thing the macro does is to declare a string variable that will hold the filename that the user chooses. TestWorkbook is the name of our string variable.
2. In Step 2, we use the On Error statement to tell 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 TestWorkbook 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 WorkbookOpenTest to True. If an error occurred, that means the workbook is not open. In that case, we set the WorkbookOpenTest to False.

Again, this is a function that can be used to evaluate any file you pass to it, via its 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 macro, “Opening a Specific Workbook Chosen by the user” but this time, we are calling the new WorkbookOpenTest function to make sure the user cannot open an already open file.

'------------------ Modules ------------------
Sub FileOpenTest()
'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 WorkbookOpenTest(FNFileOnly) = True Then
            MsgBox "The chosen file is already open"
        Else
            Workbooks.Open Filename:=FName
        End If
    End If
End Sub

If the workbook is open, it will display a message: The chosen file is already open.

How to Use This Macro