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.
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.
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:
- What is VBA in Excel?
- How to Activate Workbook Using VBA
- How to Open Excel Workbook Using VBA
- Useful Excel VBA Macro Codes Examples
- Run Macro In Another Workbook Using VBA
- Protect Excel Workbook Using VBA
- Delete Files in a Folder Using VBA in Excel
- VBA to Unprotect a Workbook
- VBA to Check If Sheet Exists