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.

Download Practice Workbook

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


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 Microfost Office 365 to do the task. You can use any available Excel version.

Check If Workbook Is Open and Close It


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.

Check If Workbook Is Open and Close It

  • 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

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.
  • 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.

Check If Workbook Is Open and Close It

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.

Check If Workbook Is Open and Close It


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

Check If Workbook Is Open and Close It

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.
  • 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.

Check If Workbook Is Open and Close It

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.

Check If 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

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.
  • 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.

Check If Workbook Is Open and Close It

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.

Check If Workbook Is Open and Close It


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

Check If Workbook Is Open and Close It

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.
  •  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.

Check If Workbook Is Open and Close It

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.

Check If 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.
  • 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

Code Breakdown

  • We declare Check_All_open_Workbook as the Sub.
  • We take Work_Book_count as Integer.
  • The For loop is used to find out all the open workbooks.
  • Workbooks.Close method is used to close all the workbooks.

  • 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.

Check If Workbook Is Open and Close It

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.


Practice Section

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


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. Please visit our website Exceldemy to explore more.

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo