Check if workbook is open, if closed open workbook
This tutorial shows how to check if a specific workbook is open and if it's closed then open the workbook through the use of VBA
METHOD 1. Check if workbook is open, in the same Excel session, if closed then open the workbook
VBA
Sub Check_if_workbook_is_open_and_open_workbook_if_closed()
'declare variables
Dim wb As Workbook
Dim FilePath As String
Dim wb As Workbook
Dim FilePath As String
FilePath = "C:\Excel\Parameters.xlsx"
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"
returnval = "fileopen"
MsgBox "File is Open"
returnval = "fileopen"
End If
Next
If returnval <> "fopen" Then
Workbooks.Open (FilePath)
End If
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.
File Path: Select the file path, including the name of the file, by changing "C:\Excel\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.
File Path: Select the file path, including the name of the file, by changing "C:\Excel\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, if closed then open the workbook
VBA
Sub Check_if_workbook_is_open()
'declare variables
Dim FilePath As String
Dim FileOpen As String
Dim FilePath As String
Dim FileOpen As String
FilePath = IsWBOpen("C:\Excel\Parameters.xlsx")
FileOpen = "C:\Excel\Parameters.xlsx"
If FilePath = True Then
MsgBox "File is Open"
Else
Workbooks.Open (FileOpen)
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.
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.
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 and if it's closed then open the workbook, using VBA.
This tutorial shows how to check if a specific workbook is open in the same Excel session or across all Excel sessions and if it's closed then open the workbook, 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 and if not then it will open the nominated workbook. The second method shows how to check if a specific workbook, including its location and name, is open in all Excel sessions and if not it will open the specific workbook. 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 | How to check if a specific workbook is open 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 |