How to check if workbook is open in VBA?

---

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.