How to check if a workbook (file) is Open or not using VBA

Method 1

In this macro, it loops through all the open workbooks and prints the name of each workbook in the Immediate window.

Option Explicit

Sub checkOpenWorkBook()
On Error GoTo err_handle
    
    Dim iCnt as Integer
    For iCnt = 1 To Application.Workbooks.Count
        Debug.Print Application.Workbooks.Item(iCnt).Name
    Next iCnt

err_handle:
    ' Debug.Print Err.Description
End Sub

Although it won’t throw any error, its always good to have error handler in your VBA code.

Anyway, the Application.Workbooks.Count gives me the count or total number of workbooks that are currently open. This includes the workbook, which is running the macro.

You can see the Immediate window to check if the workbook that you are looking is open or not.

The .Item() method of Workbooks property, takes a parameter, the index. It returns the Name of the Item (or the workbook).

.Item(index)

Instead of number, you can specify the workbook name as parameter to the Item() method. For example,

Debug.Print Application.Workbooks.Item("sample.xlsx").Name

Method 2

In this 2nd macro, I’ll show you how to filter out a particular workbook (from all the open workbooks) that you are looking for.

Here I’ll just set a condition. I know which file (workbook) I am checking.

Option Explicit

Sub checkOpenWorkBook()
On Error GoTo err_handle
    
    Dim iCnt as Integer
    Dim bYesOpen As Boolean

    For iCnt = 1 To Application.Workbooks.Count
        If Trim(Application.Workbooks.Item(iCnt).Name) = "sample.xlsx" Then
            bYesOpen = True
        End If
    Next iCnt
    
    If bYesOpen Then
        MsgBox ("Workbook is open")
    Else
        MsgBox ("Workbook is not open")
    End If

err_handle:
    ' Debug.Print Err.Description
End Sub

That’s it.

← PreviousNext →