Check If Workbook Is Open Using VBA

Sometimes, when working with VBA code, you may want to check whether a workbook is open or not.

For example, if you plan on using a workbook in your VBA code later, you may want to first quickly run a check to ensure that the work is already open.

There are a couple of simple methods I’ll show you in this article that you can use to quickly check whether a book is open or not using VBA.

Loop Through the Workbooks Collection to Check If Workbook Is Open

One of the easiest ways to check if a workbook is open or not is to go through all the workbooks and check their names.

If any of the open workbooks’ names match the one that you’re looking for, it means that the workbook is open. Else, it’s closed.

Let’s say that I want to check whether the workbook Annual Budget.xlsx is open or not.

I can do this using the below VBA code:

Sub LoopCheckIfWorkbookOpen()
Dim wb As Workbook
Dim wbName As String
Dim wbOpen As Boolean
wbName = "Annual Budget.xlsx"
wbOpen = False
For Each wb In Workbooks
If wb.Name = wbName Then
    wbOpen = True
    wb.Activate
    MsgBox "The workbook """ & wbName & """ is open."
    Exit For
End If
Next wb
If Not wbOpen Then
    MsgBox "The workbook """ & wbName & """ is not open."
End If
End Sub

When you run the code and the workbook ‘Annual Budget.xlsx’ is open, the code will activate the workbook and display a message box indicating that the workbook is open:

VBA message box when the workbook is already open

If the workbook is not open, the code shows a message box indicating that the workbook is not open:

VBA message box when the workbook is not open

Explanation of the Code

The example code loops through the ‘Workbooks’ collection (that is a collection of all the open workbooks) and compares the name ‘Annual Budget.xlsx’ with the names of the open workbooks.

If there is a match, it activates the ‘Annual Budget.xlsx’ workbook, displays a message box indicating that the workbook is open, and exits the code.

On the other hand, if there is no match, it displays a message box indicating that the workbook is not open.

Note: Instead of hardcoding the name of the workbook you want to check, you can prompt the user to enter it using the ‘InputBox’ function. To implement this, replace the statement wbName = “Annual Budget.xlsx” with wbName = InputBox (“Enter the Workbook’s Name:”).

Also read: VBA Check IF Sheet Exists

Using Error Handling to Check If Workbook Is Open

Another way to check whether the book book is open or not is by trying to set a variable (say wb) to the workbook.

This would only work if the workbook is already open, and if it is not, it would result in an error.

So if we get an error, we know that the workbook is not open, and if we don’t get an error, then we can safely assume that the workbook is open

Below is an example code that tries to check whether the workbook ‘Annual Budget.xlsm’ is open or not.

Sub ErrorHandlingCheckIfWorkbookOpen()
Dim wbName As String
Dim wb As Workbook
wbName = "Annual Budget.xlsm"
On Error Resume Next
Set wb = Workbooks(wbName)
On Error GoTo 0
If Not wb Is Nothing Then
    MsgBox wbName & " is open."
Else
    MsgBox wbName & " is not open."
End If
End Sub

When you execute the code and the workbook ‘Annual Budget.xlsm’ is open, the code will display a message box indicating that the workbook is open:

VBA Msgbox when workbook is open

If the workbook is not open, the code will display a message box stating it is not open.

Note: A regular workbook has a .xlsx extension, and a macro-enabled one has a .xlsm extension. If you are uncertain whether the workbook you want to check is open and has macros enabled, you can use the asterisk (*) wildcard character in the file extension. For example, in this case, you can assign “Annual Budget.xls*” to the ‘wbName’ variable. An asterisk character (*) represents any number of characters, so as long as the workbook extension has .xls, this would work.

Explanation of the Code

The code attempts to reference the workbook you are checking by assigning it to an object variable.

An error occurs if the workbook is not open and the code displays an appropriate message. If the workbook is open, no error occurs, and the code displays a message box stating that the workbook is open.

Note: The subroutine does not use looping, so it is slightly more efficient than the code in Method #1.

Also read: VBA Check IF Cell is Empty (ISEMPTY Function)

Using a User-Defined Function (UDF) to Check If the Workbook Is Open

You can create a UDF (User Defined Function) in VBA and call it in a subroutine to check if a workbook is open.

Suppose you want to check if the workbook ‘Annual Budget.xlsx’ is open.

You can use the following code that calls a UDF to get this done:

Sub UDFCheckIfWorkbookOpen()
Dim wbName As String
wbName = "Annual Budget.xlsx"
If CheckIfWorkbookOpen(wbName) Then
    MsgBox wbName & " is open."
Else
    MsgBox wbName & " is not open."
End If
End Sub

Function CheckIfWorkbookOpen(wbName As String) As Boolean
Dim wb As Workbook
For Each wb In Workbooks
    If wb.Name = fileName Then
        CheckIfWorkbookIsOpen = True
        Exit Function
    End If
Next wb
CheckIfWorkbookIsOpen = False
End Function

When you execute the ‘UDFCheckIfWorkbookOpen’ subroutine, it calls the ‘IsWorkbookOpen’ function and checks whether the ‘Annual Budget.xlsx’ workbook is open.

  • If the workbook is open, the subroutine displays a message box stating that the workbook is open.
VBA Msgbox that workbook is open
  • If the workbook is not open, the code displays a message box indicating that the workbook is not open.

Explanation of the Code

The ‘IsWorkbookOpen’ UDF takes a workbook name as input and iterates through all open workbooks using a For Each loop, checking each workbook’s name against the specified workbook name.

If it finds a match, the UDF returns ‘True’ and exits the loop. If it does not find a match, the UDF returns ‘False.’

Using UDF to Check If the Workbook Is Already Open at a Specific Path

You can use a User-Defined Function (UDF) to check whether another user has opened a workbook at a specific path.

Suppose you want to check if the workbook ‘\\Server1\Reports\Annual Budget.xlsx’ is already opened by another user.

You can use the following code to perform the task:

Sub CheckWorkbookOpenAtPath()
Dim Result As Boolean
Result = CheckWorkbookOpenByPath("\\Server1\Reports\Annual Budget.xlsx")
    If Result = True Then
        MsgBox "Workbook is open."
    Else
        MsgBox "Workbook is not open."
    End If
End Sub

Function CheckWorkbookOpenByPath(FileName As String)
Dim filenumber As Long, errorNumber As Long
On Error Resume Next
filenumber = FreeFile()
Open FileName For Input Lock Read As #filenumber
Close filenumber
errorNumber = Err
On Error GoTo 0
Select Case errorNumber
Case 0: CheckWorkbookOpenByPath = False
Case 70: CheckWorkbookOpenByPath = True
Case Else: Error errorNumber
End Select
End Function

When you execute the code and the ‘Annual Budget.xlsx’ workbook is open at the specified path, the code displays a message box stating that the workbook is open.

If the workbook is not open, the code returns a message box indicating that the workbook is not open.

Explanation of the Code 

The code attempts to open the ‘Annual Budget.xlsx’ workbook at the specified path in exclusive mode for input.

If another user has not opened the workbook, the error code 0 occurs. If another user has opened the workbook, a permission-related error code 70 occurs.

The logic behind this code is that if another user has already opened the target workbook, you will encounter a permission-related error if you attempt to open it for input in exclusive mode.

Display the Names of All Open Workbooks 

In situations where you want to perform an operation across multiple workbooks, you may need to ascertain which workbooks are open.

You can use the code below to print the names of the workbooks in the ‘Workbooks’ collection to the Immediate window:

Sub DisplayNamesOpenWorkbooks()
Dim wb As Workbook
For Each wb In Workbooks
    Debug.Print wb.Name
Next wb
End Sub

When you execute the code, it prints the names of the workbooks that are currently open to the Immediate window:

List of all the workbook names that are open

Note: If the Immediate window is closed, you can open it by pressing CTRL + G.

Explanation of the Code

The code iterates through each workbook in the collection of open workbooks using a ‘For Each Next’ loop, and for each workbook, it prints the workbook’s name in the Immediate window.

In this article, I’ve covered five examples where I’ve used simple VBA codes to check whether a workbook is open or not using VBA.

I hope you found this article helpful.

If you have any questions or you have any other idea about how a workbook is open or not can be checked using VBA, do let me know in the comments section.

Other Excel articles you may also like:

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.
Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster