How to Check If a Workbook is Open and Close It Using Excel VBA (5 Methods)

In the following picture, you can see that we have several workbooks. We want to check if a workbook is open and close it using VBA.

Method 1 – Checking by Using Workbook Name in VBA

Open your Excel workbook.
Go to the Developer tab and select Visual Basic.

In the VBA editor window, click Insert and choose Module.

Add the following code:

Sub Check_if_workbook_is_open_by_adding_workbook_name()
Dim work_book As Workbook
For Each work_book In Workbooks
    If work_book.Name = "Book1.xlsx" Then

       MsgBox "workbook is Open"
    End If
Next
Workbooks("Book1.xlsx").Close
End Sub

Code Breakdown

We declare Check_if_workbook_is_open_by_adding_workbook_name as the Sub.
We take work_book as WorkBook.
The For Next loop continues to check the workbook name until it finds Book1.
The If Statement is used to find out whether Book1 is close or not.
“workbook is Open” is used in the MsgBox when the workbook is open.
We used the Close method to close Book1.

Save the code, close the VBA editor, and return to your worksheet.

Run the code by going to the Developer tab, selecting Macro, and choosing Check_if_workbook_is_open_by_adding_Workbook_name.

You can see the workbook is Open in a message box.

Click OK to Close Book1.

Method 2 – Use of InputBox Function to Check If a Workbook Is Open and Close It

Follow the steps from Method 1 to open the VBA editor.

Add the following code:

Sub Using_msgbox_to_check_if_workbook_is_open()

Dim work_book As Workbook
Dim mywork_book As String

mywork_book = InputBox(Prompt:="type name of the workbook")

For Each work_book In Workbooks
    If work_book.Name = mywork_book Then
        work_book.Activate
        MsgBox "Workbook is open"
        ActiveWorkbook.Close
        Exit Sub
    End If
Next work_book

MsgBox "Workbook is closed"

End Sub

Code Breakdown

We declare Using_msgbox_to_check_if_workbook_is_open as the Sub.
We take work_book as WorkBook.
We take mywork_book as String.
We used an InputBox to show the “type name of the workbook“
The For Next loop continues to check all the workbooks’ names until it finds the Workbook that we type in the
The If Statment is used to find whether the Workbook that we type in the InputBox is open or closed.
“workbook is Open” is used in the MsgBox when the workbook is open.
We used Close method to close the workbook.
We used “workbook is closed” in another MsgBox so that when the workbook is closed, it will return “workbook is closed“.

Save the code, close the VBA editor, and return to your worksheet.

Run the code by going to the Developer tab, selecting Macro, and choosing Using_msgbox_to_check_if_workbook_is_open.

A “type name of the workbook” message box will appear.

Type in Book4.xlsx to find out if Book4 is open or not.

You can see the workbook is Closed in a message box.

Method 3 – Checking If Workbook Is Open Using File Path in VBA

In this method, we’ll use the file path of the worksheet in VBA to determine if a workbook is open and close it.

Follow these steps:

Open your Excel workbook.
Go to the Developer tab and select Visual Basic.
In the VBA editor window, click Insert and choose Module.
Add the following code:

Sub Check_if_workbook_is_open_using_file_path()
Dim file_path As String
file_path = Check_if_workbook_is_open("C:\Users\Administrator\Desktop\New folder\Book2.xlsx")
If file_path = True Then
    MsgBox "Workbook is Open"
Else
    MsgBox "workbook is Closed"
End If
Workbooks("Book2.xlsx").Close
End Sub

Function Check_if_workbook_is_open(FileName As String)
Dim file_no As Long
Dim error_no As Long
On Error Resume Next
file_no = FreeFile()
Open FileName For Input Lock Read As #file_no
Close file_no
error_no = Err
On Error GoTo 0
Select Case error_no
Case 0
Check_if_workbook_is_open = False
Case 70
Check_if_workbook_is_open = True
Case Else
Error error_no
End Select
End Function

Code Breakdown

We declare Check_if_workbook_is_open_using_file_path as the Sub.
We take file_path as String.
The If Statement is used to check whether the workbook defined by the file_path is open or closed.
Close method is used to close Book2.
Check_if_workbook_is_open is used as Function.
We take the file_no as Long.
We take the error_no as Long, therefore, the code will return no error if it does not find any file in the defined path.
We used Case 0 to return False when the workbook is open.
We used Case 70 to return True when the workbook is open.

Save the code, close the VBA editor, and return to your worksheet.

Run the code by going to the Developer tab, selecting Macro, and choosing Check_if_workbook_is_open_using_file_path.

You can see the workbook is Open in a message box.

Click OK to close Book2.

Method 4 – Using User-Defined Function to Check If Workbook Is Open

In this method, we’ll create a user-defined function in VBA to check if a workbook is open and close it.

Follow these steps:

Open the VBA editor as described in Method 1.
Enter the following code:

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

Sub User_defined_function_to_check_workbook_open_or_closed()
    Dim x_ret As Boolean
    x_ret = Check_if_workbook_is_open("Book3.xlsx")
    If x_ret Then
        MsgBox "The workbook is open", vbInformation, "Checking Workbook Open or Not"

    Else
        MsgBox "The workbook is not open", vbInformation, "Checking Workbook Open or Not"
    End If
    Workbooks("Book3.xlsx").Close
End Sub

Code Breakdown

Check_if_workbook_is_open is used as Function.
We take the x_workbook as Workbook.
We declare User_defined_function_to_check_workbook_open_or_closed as the Sub.
We take x_ret as Boolean.
The If Statement is used to check whether the file defined by the file_path is open or closed.
“The Workbook is Open” is used in the MsgBox, when the workbook is open.
We used “workbook is closed” in another MsgBox so that when the workbook is closed, it will return “workbook is closed“.
We used the Close method to close the workbook.

Save the code, close the VBA editor, and return to your worksheet.

Run the code by going to the Developer tab, selecting Macro, and choosing User_defined_function_to_check_workbook_open_or_closed.

You can see the workbook is Open in a message box.

Click OK to close Book3.

Method 5 – Checking If All Workbooks Are Open and Closing Them

This approach is useful for closing all open workbooks, including the active workbook.

Follow these steps:

Open your Excel workbook.
Go to the Developer tab and select Visual Basic.
In the VBA editor window, click Insert and choose Module.
Add the following code:

Sub Check_All_open_Workbook()
Dim Work_Book_count As Integer
Work_Book_count = Workbooks.Count
ThisWorkbook.Worksheets.Add
ActiveSheet.Range("B4").Activate
For workbook_count = 1 To Work_Book_count
Range("B4").Offset(workbook_count - 2, 0).Value = Workbooks(workbook_count).Name
Next workbook_count
Workbooks.Close
End Sub