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 WorkbookDim wbName As StringDim wbOpen As BooleanwbName = "Annual Budget.xlsx"wbOpen = FalseFor Each wb In WorkbooksIf wb.Name = wbName Then wbOpen = True wb.Activate MsgBox "The workbook """ & wbName & """ is open." Exit ForEnd IfNext wbIf Not wbOpen Then MsgBox "The workbook """ & wbName & """ is not open."End IfEnd SubWhen 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:
If the workbook is not open, the code shows a message box indicating that 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 StringDim wb As WorkbookwbName = "Annual Budget.xlsm"On Error Resume NextSet wb = Workbooks(wbName)On Error GoTo 0If Not wb Is Nothing Then MsgBox wbName & " is open."Else MsgBox wbName & " is not open."End IfEnd SubWhen 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:
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 StringwbName = "Annual Budget.xlsx"If CheckIfWorkbookOpen(wbName) Then MsgBox wbName & " is open."Else MsgBox wbName & " is not open."End IfEnd SubFunction CheckIfWorkbookOpen(wbName As String) As BooleanDim wb As WorkbookFor Each wb In Workbooks If wb.Name = fileName Then CheckIfWorkbookIsOpen = True Exit Function End IfNext wbCheckIfWorkbookIsOpen = FalseEnd FunctionWhen 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.
- 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 BooleanResult = CheckWorkbookOpenByPath("\\Server1\Reports\Annual Budget.xlsx") If Result = True Then MsgBox "Workbook is open." Else MsgBox "Workbook is not open." End IfEnd SubFunction CheckWorkbookOpenByPath(FileName As String)Dim filenumber As Long, errorNumber As LongOn Error Resume Nextfilenumber = FreeFile()Open FileName For Input Lock Read As #filenumberClose filenumbererrorNumber = ErrOn Error GoTo 0Select Case errorNumberCase 0: CheckWorkbookOpenByPath = FalseCase 70: CheckWorkbookOpenByPath = TrueCase Else: Error errorNumberEnd SelectEnd FunctionWhen 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 WorkbookFor Each wb In Workbooks Debug.Print wb.NameNext wbEnd SubWhen you execute the code, it prints the names of the workbooks that are currently open to the Immediate window:
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:
