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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top