Table of Contents
To check if a workbook is already open using VBA, you can use the “Workbooks” collection object and the “IsOpen” method. This method allows you to check if a specific workbook is open by using the workbook’s name or file path. If the workbook is open, the method will return a Boolean value of True, otherwise it will return False. This can be useful in automating tasks and avoiding errors when working with multiple workbooks in VBA.
VBA: Check if Workbook is Open (With Example)
You can use the following syntax in VBA to check if a particular workbook is currently open.
Sub CheckWorkbookOpen()
Dim resultCheck As Boolean
Dim wb As Workbook
Dim specific_wb As String
On Error Resume Next
specific_wb = InputBox("Check if this workbook is open:")
Set wb = Application.Workbooks.Item(specific_wb)
resultCheck = Not wb Is NothingIf resultCheck Then
MsgBox "Workbook is open"
Else
MsgBox "Workbook is not open"
End If
End Sub
When this macro is run, an input box will appear where a user can type in the name of an Excel workbook and the macro will produce a message box with one of the following results:
- “Workbook is open”
- “Workbook is not open”
The following example shows how to use this syntax in practice.
Example: How to Check if Workbook is Open Using VBA
Suppose we currently have two workbooks open with the following names:
- my_workbook1.xlsx
- my_workbook2.xlsx
Suppose we would like to check if the workbook called my_workbook1.xlsx is currently open.
We can create the following macro to do so:
Sub CheckWorkbookOpen()
Dim resultCheck As Boolean
Dim wb As Workbook
Dim specific_wb As String
On Error Resume Next
specific_wb = InputBox("Check if this workbook is open:")
Set wb = Application.Workbooks.Item(specific_wb)
resultCheck = Not wb Is NothingIf resultCheck Then
MsgBox "Workbook is open"
Else
MsgBox "Workbook is not open"
End If
End Sub
Once we run this macro, a box will appear where I can type in my_workbook1.xlsx in the input box:
Once I click OK, the macro will produce the following message box:
The macro correctly outputs “Workbook is open” to indicate that a workbook with this name is currently open.
Now suppose that I instead typed in the name of a workbook that is not currently open:
Once I click OK, the macro will produce the following message box:
The macro correctly outputs “Workbook is not open” to indicate that a workbook with this name is not currently open.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: