How to quickly check if a file (workbook) is open or closed in Excel?

Excel daily work, you open multiple workbooks at the same time for working, but sometimes, the hundreds of workbooks may mess your head to remember if a specific workbook is open or closed. Give up the traditional method on checking files one by one, here I introduce you the tricks to quickly find a workbook is open or closed.

Check if a workbook is open or closed with VBA

Check if a workbook is open or closed with Kutools for Excel

Check if a workbook is open or closed with VBA

Here is a VBA code you can run to check if a specific workbook is open or closed.

1. Press Alt + F11 keys to open Microsoft Visual Basic for Applications window.

2. Click Insert > Module and then copy and paste the VBA to the new Module window.

VBA: Check if a workbook open or closed

Function IsWorkBookOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function

Sub Sample()
    Dim xRet As Boolean
    xRet = IsWorkBookOpen("combine.xlsx")
    If xRet Then
        MsgBox "The file is open", vbInformation, "Kutools for Excel"
    Else
        MsgBox "The file is not open", vbInformation, "Kutools for Excel"
    End If
End Sub

3. And press F5 key to run this vba, and a dialog pops out to remind you the specific workbook is open or not.
    

Tip: In the above VBA, “combine” is the workbook name you want to check, you can it as you need.

Check if a workbook is open or closed with Kutools for Excel

If you are no familiar with VBA, you can check if a workbook is open by Kutools for Excel, with its’s Navigation pane, which will help you can clearly view all open workbooks in the workbook list of the pane.

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Navigation to enable the Navigation pane. See screenshot:

2. Then click Workbook & Sheet button to expand the pane to go to Workbook & Sheet section. And you can view all open workbooks in the upper list. See screenshot:

With Navigation pane, you also can quickly switch between the workbooks or sheets in the list.

Click here to know more about Navigation.

Demo: How to create a dyanmic list of sheet names in Excel?