Check if workbook is open
This tutorial shows how to check if a specific workbook is open through the use of VBA
METHOD 1. Check if workbook is open in the same Excel session
VBA
Sub Check_if_workbook_is_open()
'declare a variable
Dim wb As Workbook
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name = "Parameters.xlsx" Then
'this message will appear if one of the open workbooks is Parameters.xlsx
MsgBox "File is Open"
MsgBox "File is Open"
End If
Next
End Sub
ADJUSTABLE PARAMETERS
File Name: Select the file name of a workbook that you want to check if it's open by changing the file name "Parameters.xlsx" in the VBA code.
Message: Select the message that you want to be displayed if the workbook is open by changing the message "File is Open" in the VBA code.
File Name: Select the file name of a workbook that you want to check if it's open by changing the file name "Parameters.xlsx" in the VBA code.
Message: Select the message that you want to be displayed if the workbook is open by changing the message "File is Open" in the VBA code.
ADDITIONAL NOTES
Note 1: This VBA code will check Excel workbooks in the same session as the workbook from which you are running this VBA code.
Note 1: This VBA code will check Excel workbooks in the same session as the workbook from which you are running this VBA code.
METHOD 2. Check if workbook is open across all Excel sessions
VBA
Sub Check_if_workbook_is_open()
'declare a variable
Dim FilePath As String
Dim FilePath As String
FilePath = IsWBOpen("C:\Excel\Parameters.xlsx")
If FilePath = True Then
MsgBox "File is Open"
Else
MsgBox "File is Closed"
End If
End Sub
Function IsWBOpen(FileName As String)
'declare variables
Dim FileNo As Long
Dim ErrorNo As Long
Dim FileNo As Long
Dim ErrorNo As Long
On Error Resume Next
FileNo = FreeFile()
FileNo = FreeFile()
Open FileName For Input Lock Read As #FileNo
Close FileNo
Close FileNo
ErrorNo = Err
On Error GoTo 0
Select Case ErrorNo
Case 0
IsWBOpen = False
IsWBOpen = False
Case 70
IsWBOpen = True
IsWBOpen = True
Case Else
Error ErrorNo
Error ErrorNo
End Select
End Function
ADJUSTABLE PARAMETERS
File Path and Name: Select the file path, including the name of the file, by changing "C:\Excel\Parameters.xlsx" in the VBA code.
Message if Workbook is Open: Select the message that you want to be displayed if the workbook is open by changing the message "File is Open" in the VBA code.
Message if Workbook is Closed: Select the message that you want to be displayed if the workbook is closed by changing the message "File is Closed" in the VBA code.
File Path and Name: Select the file path, including the name of the file, by changing "C:\Excel\Parameters.xlsx" in the VBA code.
Message if Workbook is Open: Select the message that you want to be displayed if the workbook is open by changing the message "File is Open" in the VBA code.
Message if Workbook is Closed: Select the message that you want to be displayed if the workbook is closed by changing the message "File is Closed" in the VBA code.
ADDITIONAL NOTES
Note 1: This VBA code will check workbooks in all Excel sessions.
Note 2: This method creates a User Defined Function called 'IsWBOpen' and then runs the VBA code.
Note 1: This VBA code will check workbooks in all Excel sessions.
Note 2: This method creates a User Defined Function called 'IsWBOpen' and then runs the VBA code.
EXPLANATION
This tutorial shows how to check if a specific workbook is open in the same Excel session or across all Excel sessions, using VBA.
This tutorial shows how to check if a specific workbook is open in the same Excel session or across all Excel sessions, using VBA.
The first method shows how to check if a workbook, with a specific name, is open in the same Excel session in which the VBA code is run. The second method shows how to check if a specific workbook, including its location and name, is open in all Excel sessions. This is achieved by creating a User Defined Function and then applying this in a macro.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Check if workbook is open, if closed open workbook | How to check if a specific workbook is open and if it's closed then open the workbook using VBA | |
| Open an Excel workbook | How to open a single workbook using Excel, VBA and Shortcut | |
| Open an Excel workbook as Read-Only | How to open a single workbook as Read-Only using Excel and VBA |