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 Nothing
If 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 Nothing
If 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:
VBA: How to Count Number of Sheets in Workbook
VBA: How to Insert Multiple Columns
VBA: How to Insert Multiple Rows
