In VBA Check if a Workbook is Open

JillClaus

New Member
Joined
Jun 5, 2002
Messages
3
I'm using Excel 97.

I wrote some VBA code that loops through all of the workbooks in a specified subdirectory and popolates each of them with data from a MS Access 97 database. It works well, but I'm stuck on one thing....

How can I check to see if a specific workbook is currently open (or closed)?

Any help would be greatly appreciated.

Thanks,
Jill
 

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
Hi Jill --

I am doing something similar. Here is how I do it:

Workbooks.Open FileName:= _
"file.xls", _
updatelinks:=3, WriteResPassword:="password"
'check to see if read only
If ActiveWorkbook.ReadOnly Then
ActiveWorkbook.Saved = True
ActiveWorkbook.Close

This assumes you have passwords on your files. The VBA should open the file and if someone else has it open, it will open as read only. It will see that, then skip save, then close. Let me know if it works for you.

EdE
 

Ricky Morris

Active Member
Joined
Mar 31, 2002
Messages
363
Try:

Sub Check_If_Workbook_Open()
Dim wbk As Workbook
For Each wbk In Workbooks
If wbk.Name = "Your Workbook.xls" Then
MsgBox "workbook is open"
End If
Next
End Sub
 

JillClaus

New Member
Joined
Jun 5, 2002
Messages
3
The code I'm writing to manipulate the Excel spreadsheet is in MSAccess, so I tried...

If xl.Application.Workbooks.Name = MyFileName Then
xl.Application.Workbooks(FileName).Close
End If

But I got an error saying "Object doesn't support this property or method."

Any ideas?

Jill
 

JillClaus

New Member
Joined
Jun 5, 2002
Messages
3
Good idea, but when I tried...

If xl.Application.Workbooks(FileName).ReadOnly = True Then
xl.Application.Workbooks(FileName).Close End If

But got the error "Subscript out of range" if the file was already closed.

Jill
 
L

Legacy 103420

Guest
Try:

Sub Check_If_Workbook_Open()
Dim wbk As Workbook
For Each wbk In Workbooks
If wbk.Name = "Your Workbook.xls" Then
MsgBox "workbook is open"
End If
Next
End Sub
Thanks! This one helped me.
#littleBIGthings
 

LuisV805

New Member
Joined
Jan 9, 2014
Messages
18
I've had this same issue and been working it out for days. FYI to anyone else out there, here is how I solved it:

Code:
TargetWb = "Your Workbook.xls"

For Each Workbook In Workbooks
    If Workbook.FullName = TargetWb Then Workbook.Close (False)
Next Workbook
Workbooks.Open(TargetWb).Activate
Hope this saves somebody the hours of aggravation I spent trying to work out an elegant solution to this.
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
just for completeness, you need not to loop through workbooks to check if it is open

Code:
Sub WBG()
Dim WB As Workbook
On Error Resume Next
    Set WB = Workbooks("Your Workbook.xls")
    If Err Then MsgBox "The workbook is not open"
On Error GoTo 0
End Sub
 

tbro

New Member
Joined
Oct 7, 2012
Messages
10
Sometimes I actually have a workbook open in the background while running a macro fetching data from it.
When the workbook is already open, I don't want to close it. So here's what I do:

Code:
Sub Fetch_data()
Dim WbOpen As Boolean
Dim Wb2 As Workbook, Sh2 As Worksheet
    
    ' Check if workbook is open
    On Error Resume Next
    Set Wb2 = Workbooks("MyWorkbook.xls")
    On Error GoTo 0
    If Wb2 Is Nothing Then
        ' If workbook was NOT open, we'll open it
        ' We'll use the parameter WbOpen to remember whether the workbook was open or not
        Set Wb2 = Workbooks.Open("C:\Excel\MyWorkbook.xls", ReadOnly:=True) ' I prefer readonly when fetching data
        Set Sh2 = Wb2.Sheets("MySheet")
        WbOpen = False
    Else
        ' If the workbook was open, we'll just assign the sheet and WbOpen
        Set Sh2 = Wb2.Sheets("MySheet")
        WbOpen = True
    End If
    
    ' Code here...
    ' ............
    ' Code ended..
    
    ' If the workbook was NOT open, we'll close it
    If WbOpen = False Then Wb2.Close SaveChanges:=False
End Sub
 

F3rzen

New Member
Joined
Oct 20, 2014
Messages
1
Jill this just work fine for me... the error handler is see if it is open it will set work workbook but if not only it is going to go to the workbook is not open


Sub Sample()
Dim BA As Workbook


On Error GoTo Handler
Set BA = Workbooks("BA.xlsm")

If BA.Name = "BA.xlsm" Then
MsgBox "Workook is open"
Else
Handler:
MsgBox "Workbook is closed"
End If


End Sub






I'm using Excel 97.

I wrote some VBA code that loops through all of the workbooks in a specified subdirectory and popolates each of them with data from a MS Access 97 database. It works well, but I'm stuck on one thing....

How can I check to see if a specific workbook is currently open (or closed)?

Any help would be greatly appreciated.

Thanks,
Jill
 

Forum statistics

Threads
1,078,213
Messages
5,338,891
Members
399,265
Latest member
aj17x55

Some videos you may like

This Week's Hot Topics

Top