Excel VBA: Check If Workbook Is Open and Close It (5 Examples)

If you want to check if a workbook is open and close it using VBA then you have come to the right place. Here, we will walk you through 5 easy and effective methods to do the task smoothly.

5 Examples of Excel VBA to Check If Workbook Is Open and Close It

In the following picture, you can see that we have several workbooks. Next, we want to check if a workbook is open and close it using VBA. To do so, we will go through 5 easy and effective methods. Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.

1. Checking by Using Workbook Name in VBA

In this method, we will add the workbook name in VBA to check if a workbook is open and close it.

Let’s go through the following steps to do the task.

Steps:

• First, we will go to the Developer tab >> select Visual Basic.

This will bring out a VBA editor window.

Here, you can press ALT+F11 to bring out the VBA editor window.

Next, a VBA editor window will appear.

• At this point, from the Insert tab >> select Module.

• After that, in the Module, we will type 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

• Then, we will Save the code >> close the VBA editor window >> return to our worksheet.

Furthermore, we will run the code.

• To do so, from the Developer tab >> select Macro.

At this point, a Macro dialog box will pop up.

• Afterward, in the Macro Name box >> select Check_if_workbook_is_open_by_adding_workbook_name
• Moreover, click on the Run button.

Here, you can press ALT+F8 to bring out the Macro dialog box and Run the code.

As a result, you can see the workbook is Open in a message box.

Therefore, Book1 is open.

• Then, we will click OK.

This will close Book1.

Hence, you can check if a workbook is open and close it using VBA.

Read More: Excel VBA: Close Workbook Without Saving

2. Use of InputBox Function to Check If Workbook Is Open and Close It

In this method, we will use an input box in VBA to check if a workbook is open and close it.

Steps:

Let’s go through the following steps to do the task.

• First of all, we followed the steps of Method-1 to bring out the Module.
• Then, we type the following code in the Module.

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

• Next, we will Save the code >> close the VBA editor window >> return to our worksheet.

Moreover, we will run the code.

• To do so, from the Developer tab >> select Macro.

Next, a Macro dialog box will pop up.

• Afterward, in the Macro Name box >> select Using_msgbox_to_check_if_workbook_is_open
• In addition, click on the Run button.

Here, you can press ALT+F8 to bring out the Macro dialog box and Run the code.

Afterward, a type name of the workbook message box will appear.

• Here, we typed Book4.xlsx to find out if Book4 is open or not.

As a result, you can see the workbook is Closed in a message box.

That means Book4 is closed.

Hence, you can check if a workbook is open and close it using VBA.

3. Checking If Workbook Is Open Using File Path in VBA

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

Steps:

Let’s go through the following steps to do the task.

• First, we followed the steps of Method-1 to bring out the Module.
• After that, we type the following code in the Module.

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

• At this point, we will Save the code >> close the VBA editor window >> return to our worksheet.

Next, we will run the code.

• To do so, from the Developer tab >> select Macro.

Next, a Macro dialog box will pop up.

• Afterward, in the Macro Name box >> select Check_if_workbook_is_open_using_file_path.
• Moreover, click on the Run button.

Here, you can press ALT+F8 to bring out the Macro dialog box and Run the code.

As a result, you can see the workbook is Open in a message box.

That means Book2 is open.

• Then, we will click OK.

This will close Book2.

Hence, you can check if a workbook is open and close it using VBA.

Read More: How to Close Workbook at Specific Time Using Excel VBA

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

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

Steps:

Let’s go through the following steps to do the task.

•  In the beginning, we followed the steps of Method-1 to bring out the Module.
• After that, we will type the following code in the Module.

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

•  At this point, we will Save the code >> close the VBA editor window >> return to our worksheet.

Furthermore, we will run the code.

• To do so, from the Developer tab >> select Macro.

• Afterward, in the Macro Name box >> select  User_defined_function_to_check_workbook_open_or_closed.
• Along with that, click on the Run button.

Here, you can press ALT+F8 to bring out the Macro dialog box and Run the code.

Therefore, you can see the workbook is Open in a message box.

As a result, Book3 is open.

• Then, we will click OK.

This will close Book3.

Hence, you can check if a workbook is open and close it using VBA.

5. Checking If All Workbooks Are Open or Not Then Close It

In this method, we will check all open workbooks and close them. This method is helpful to close all the open workbooks along with the active workbook.

Steps:

Let’s go through the following steps to do the task.

• In the beginning, we followed the steps of Method-1 to bring out the Module.
• Next, we type the following code in the Module.

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

• Next, we will Save the code >> close the VBA editor window >> return to our worksheet.

Furthermore, we will run the code.

• To do so, from the Developer tab >> select Macro.

Next, a Macro dialog box will pop up.

• Afterward, in the Macro Name box >> select Check_All_open_Workbook.
• In addition, click on the Run button.

Here, you can press ALT+F8 to bring out the Macro dialog box and Run the code.

At this point, you can see the names of all the open workbooks along with the active workbook on a new sheet of the active workbook.

• Next, we click on Save to save the active workbook.

After that, all the workbooks along with the active workbook will be closed.

Read More: Excel VBA: Save and Close Workbook

Practice Section

You can download the above Excel file to practice the explained methods.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Conclusion

Here, we tried to show you 5 methods to check if a workbook is open and close it using VBA. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

Related Article

• Excel VBA: Save and Close Workbook Without Prompt