In VBA Check if a Workbook is Open

[JillClaus]

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]

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]

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]

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]

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

 

[Legacy 103420]

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]

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:

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]

just for completeness, you need not to loop through workbooks to check if it is open

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]

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:

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]

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