Check If Workbook Is Open Using VBA

Checking whether a workbook is open using VBA can be helpful in several scenarios.

For example, if your VBA code is intended to open or modify a particular workbook, you should first check if it is already open.

This way, you can determine whether to use the existing instance of the workbook or take appropriate actions, such as notifying the user whether the workbook is open.

In this tutorial, I will show you six examples of checking if a workbook is open using VBA.

Table of Contents

Example #1: Check If a Workbook Specified in the Code Is Open

We can use the ‘For Each Next’ loop to iterate through the workbooks in the ‘Workbooks’ collection and check whether a particular workbook is open.

The following example code checks whether the “Sales Report.xlsx” workbook is open and notifies the user via a message box:

Sub CheckIfWorkbookOpen1()

    ' Declare the variables
    Dim wb As Workbook
    Dim wbName As String

    Dim workbookOpen As Boolean

    ' Set the workbook name to search for
    wbName = "Sales Report.xlsx"
    
    ' Initialize the workbookOpen variable to False
    workbookOpen = False
    
    ' Loop through each open workbook
    For Each wb In Workbooks
        ' Check if the current workbook's name matches the target name
        If wb.Name = wbName Then
            ' Set workbookOpen to True if a match is found
            workbookOpen = True

            ' Activate the found workbook
            wb.Activate

            ' Display a message box confirming the workbook is open
            MsgBox "The workbook """ & wbName & """ is open."

            ' Exit the For loop as the workbook is found
            Exit For
        End If
    Next wb

    ' Check if the workbook was not found and display message
    If Not workbookOpen Then
        MsgBox "The workbook """ & wbName & """ is not open."
    End If

End Sub

This macro checks whether a workbook with a specified name (“Sales Report.xlsx”) is currently open.

It iterates through all open workbooks and compares their names to the target name.

If a match is found, it sets the variable workbookOpen to True, activates the matching workbook, and displays a message box confirming that the workbook is open.

Message box showing that the workbook is open

If no matching workbook is found, it displays a message box stating that the workbook is not open.

Also read: Create New Workbook Using VBA in Excel

Example #2: Prompt User for Workbook Name and Check Whether It is Open

Instead of hard-coding the name of a workbook in the code, we can prompt the user for a workbook name and check if it is open.

The code below prompts the user for a workbook name via an InputBox, checks whether the workbook is open, and notifies the user with a message box:

Sub CheckIfWorkbookOpen2()

    ' Declare variable
    Dim wb As Workbook

    Dim wbName As String

    ' Prompt the user to enter the workbook name
    wbName = InputBox(Prompt:="Enter the workbook name.")

    ' Loop through each workbook in the collection of open workbooks
    For Each wb In Workbooks
        ' Check if the current workbook's name matches the entered name
        If wb.Name = wbName Then
            ' Activate the workbook if it's the one we're looking for
            wb.Activate
            ' Inform the user that the workbook is open
            MsgBox "Workbook is open."
            ' Exit the subroutine as the workbook has been found
            Exit Sub
        End If
    Next wb

    ' If the loop completes without finding the workbook, inform the user that it's not open
    MsgBox "Workbook is not open."

End Sub

The code displays an input box where the user can enter a workbook name, which the code stores in a variable.

Subsequently, the code utilizes a ‘For Each Next’ loop to iterate through all the currently open workbooks and checks if any of them have the same name as the one provided by the user.

If a match is found, it activates the workbook (brings it to the front of the screen), displays a message box indicating the workbook is open, and exits the subroutine.

If no match is found after looping through all open workbooks, a message box indicates that the specified workbook is not open.

Note: If you prefer not to use the ‘For Each Next’ construct in your code, you can use the following code that uses an alternative approach:

Sub CheckIfWorkbookOpen2_1()

    ' Declare a String variable to store the workbook name
    Dim wbName As String

    ' Declare a Workbook variable
    Dim wb As Workbook

    ' Temporarily disable error reporting for the next line
    On Error Resume Next

    ' Prompt the user to enter the workbook name and store it in wbName
    wbName = Application.InputBox(Prompt:="Enter the workbook name.")

    ' Attempt to set the wb variable to the workbook with the name entered by the user
    ' If the workbook is not open, this line would normally cause an error
    Set wb = Application.Workbooks(wbName)

    ' Check if the wb variable was successfully set
    If wb Is Nothing Then
        ' If wb is Nothing, the workbook is not open or does not exist
        MsgBox "The Workbook is not open."
    Else
        ' If wb is not Nothing, the workbook is open
        MsgBox "The Workbook is open."
    End If

    ' Resume normal error reporting
    On Error GoTo 0

End Sub

The code displays an input box prompting the user to enter a workbook’s name. The entered name is stored in a variable.

The code then attempts to set the variable to reference the workbook with the specified name. If the workbook is open, the variable references it; otherwise, it is set to ‘Nothing.’

If the variable is set to ‘Nothing,’ the workbook is not open. A message box is displayed indicating the workbook is not open.

If the variable references the specified workbook, it means it is open. A message box is displayed indicating the workbook is open.

Also read: How to Delete a Sheet in Excel Using VBA

Example #3: Use a User-Defined Function to Check if a Particular Workbook is Open

We can utilize a user-defined function (UDF) in a subroutine to determine whether a specified workbook is open.

The following subroutine calls the ‘IsWorkbookOpen’ UDF and uses it to check whether the specified filename is open:

Sub CheckIfWorkbookOpen_UDF()

    ' Declare a String variable to store the file name
    Dim fileName As String

    ' Set the file name of the workbook to check
    fileName = "Sales Report.xlsx"

    ' Call the IsWorkbookOpen function to check if the workbook is open
    If IsWorkbookOpen(fileName) Then
        ' Display a message if the workbook is open
        MsgBox "Workbook is open."
    Else
        ' Display a message if the workbook is not open
        MsgBox "Workbook is not open."
    End If
End Sub


Function IsWorkbookOpen(fileName As String) As Boolean
    ' Declare a Workbook variable for iteration
    Dim wb As Workbook

    ' Loop through each open workbook
    For Each wb In Workbooks
        ' Check if the current workbook's name matches the specified file name
        If wb.Name = fileName Then
            ' Return True if a match is found
            IsWorkbookOpen = True
            ' Exit the function immediately after finding a match
            Exit Function
        End If
    Next wb

    ' Return False if no matching workbook is found
    IsWorkbookOpen = False

End Function

This ‘IsWorkbookOpen’ function iterates through all open workbooks and checks if the name of any workbook matches the specified filename.

If a match is found, the function returns ‘True,’ the sub-routine displays a message box indicating the workbook is open; otherwise, it returns ‘False,’ and the sub-routine displays a message box indicating the workbook is not open.

To use this function, enter the following formula in a cell (where A1 contains the name of the workbook that you want to check:

=IsWorkbookOpen(A2)

Example #4: Use Wildcard Characters to Check If Workbook is Open

You can use wildcard characters in VBA to check if a workbook with a particular pattern in its name is open.

The following example code checks if the workbook with the specified pattern is open:

Sub CheckIfWorkbookOpen_Wildcard()

    ' Declare a String variable to store the workbook name pattern
    Dim wbNamePattern As String

    ' Declare a Workbook variable for iteration
    Dim wb As Workbook

    ' Set the workbook name pattern
    wbNamePattern = "*Sales*"

    ' Loop through each workbook in the collection of open workbooks
    For Each wb In Workbooks
        ' Check if the current workbook's name matches the specified pattern
        If wb.Name Like wbNamePattern Then
            ' Display a message with the name of the matching workbook
            MsgBox "Workbook '" & wb.Name & "' is open."
            ' Exit the Sub procedure after finding the first match
            Exit Sub
        End If
    Next wb

    ' Display a message if no workbook matches the pattern
    MsgBox "No matching workbook is open."

End Sub

The code uses a ‘For Each Next’ loop to go through all the open workbooks and verify if the name of a workbook matches a specific pattern.

The pattern uses an asterisk (*) as a wildcard to represent any characters that can appear before and after the word “Sales.”

A message box indicates that the workbook is open if a match is found and the loop is exited.

If the loop completes without finding a match, a message box indicates no matching workbook is open.

Also read: How to Close Workbook Using VBA

Example #5: Check If a Workbook Is Opened By Another User

In scenarios where multiple users can access the same Excel workbook, it can be helpful to check if another user has opened a workbook to, for example, prevent data conflicts.

You can adapt the following code to your requirements to check if another user has opened a workbook:

Sub CheckWorkbookOpenAnotherUser()

    ' Workbook variable declaration
    Dim wb As Workbook

    ' File path of the workbook
    Dim fileName As String
    fileName = "C:\Excel Tutorials\Sales Report.xlsm"

    ' Disable error reporting temporarily
    On Error Resume Next

    ' Try opening the workbook in read-only mode
    Set wb = Workbooks.Open(fileName, ReadOnly:=True)

    ' Re-enable normal error handling
    On Error GoTo 0

    ' Check if the workbook could be opened
    If wb Is Nothing Then
        ' Inform if the workbook is already open
        MsgBox "The workbook is opened by another user."
    Else
        ' Inform if the workbook is not open and close it
        MsgBox "The workbook is not opened by another user."
        wb.Close SaveChanges:=False
    End If

End Sub

This code attempts to open the workbook in read-only mode and sets the object variable to the workbook reference if it is successful.

A message box indicates that the workbook is not opened by another user, and the workbook is closed without saving changes.

If another user has opened the workbook, the ‘Workbooks.Open’ method raises an error, and the object variable is set to nothing. A message box indicates that the workbook is opened by another user.

Also read: How to Save Workbook Using VBA

Example #6: Display the Names of All Open Workbooks

You can use the following code to display the names of all open workbooks in a given instance of Excel:

Sub DisplayOpenWorkbookNames()

    ' Declare a Workbook variable for iteration
    Dim wb As Workbook

    ' Loop through each open workbook
    For Each wb In Workbooks
        ' Print the name of each workbook to the Immediate Window
        Debug.Print wb.Name
    Next wb

End Sub

The code loops through all open workbooks in the given instance of Excel and prints the name of each workbook to the Immediate Window in the VB Editor.

In this tutorial, I showed you six examples of checking if a workbook is open using VBA. We hope you found the tutorial helpful.

Other Excel VBA articles you may also like: