Test if Workbook is already open ?

[dim]

Hi all,

I've run into an unforseen hiccup in my code. I want to test if a workbook
is already open so that I don't try to open it again! In one area I open a
workbook called 3.xls using buttons in my main book called 73.xls.

I want to use a statement, so that if 3.xls is already open, I won't execute
the code Workbooks.Open section. I'm sure its a simple IF THEN ELSE, but with
my effort below I'm getting an "Invalid or Unqualifed Reference" error on the
first line when I try to re-execute the code with 3.xls already open. I
received no error when 3.xls was not open.

I dont have to use an IF statement, one it works I'll be happy....

Thanks

Private Sub CommandButton6_Click()
Application.ScreenUpdating = False
UserForm4.Hide
If Workbooks("3.xls") = .Open Then
Windows("73.xls").Activate
Sheets("CurrentEmployees").Select
Application.ScreenUpdating = True
Else
Workbooks.Open Filename:= _
"C:\Program Files\systems\MyProgram\Data1\3.xls"
Windows("73.xls").Activate
Sheets("CurrentEmployees").Select
Application.ScreenUpdating = True
End If

End Sub

 

[Ken Johnson]

Hi all,

I've run into an unforseen hiccup in my code. I want to test if a workbook
is already open so that I don't try to open it again! In one area I open a
workbook called 3.xls using buttons in my main book called 73.xls.

I want to use a statement, so that if 3.xls is already open, I won't execute
the code Workbooks.Open section. I'm sure its a simple IF THEN ELSE, but with
my effort below I'm getting an "Invalid or Unqualifed Reference" error on the
first line when I try to re-execute the code with 3.xls already open. I
received no error when 3.xls was not open.

I dont have to use an IF statement, one it works I'll be happy....

Thanks

Private Sub CommandButton6_Click()
Application.ScreenUpdating = False
UserForm4.Hide
If Workbooks("3.xls") = .Open Then
Windows("73.xls").Activate
Sheets("CurrentEmployees").Select
Application.ScreenUpdating = True
Else
Workbooks.Open Filename:= _
"C:\Program Files\systems\MyProgram\Data1\3.xls"
Windows("73.xls").Activate
Sheets("CurrentEmployees").Select
Application.ScreenUpdating = True
End If

End Sub

One way...

Add this function to your project then call it up in your Sub to
determine if the workbook is open or not...

Public Function IsWorkbookOpen(stName As String) As Boolean
'IsWorkbookOpen returns True if stName is a member
'of the Workbooks collection. Otherwise it returns False
'stName must be provided as a filename without path

Dim Wkb As Workbook

On Error Resume Next
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then
IsWorkbookOpen = True
End If
End Function

Function straight from John Green's "Excel 2000 VBA Programmer's
Reference"

Ken Johnson

 

[Rick Rothstein \(MVP - VB\)]

I've run into an unforseen hiccup in my code. I want to test if a

One way...

Add this function to your project then call it up in your Sub to
determine if the workbook is open or not...

Public Function IsWorkbookOpen(stName As String) As Boolean
'IsWorkbookOpen returns True if stName is a member
'of the Workbooks collection. Otherwise it returns False
'stName must be provided as a filename without path

Dim Wkb As Workbook

On Error Resume Next
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then
IsWorkbookOpen = True
End If
End Function

Function straight from John Green's "Excel 2000 VBA Programmer's
Reference"

I would think this way would work also...

Public Function IsWorkbookOpen(WrkBk As String) As Boolean
Dim WB As Workbook
For Each WB In Workbooks
If StrComp(WB.Name, WrkBk, vbTextCompare) = 0 Then
IsWorkbookOpen = True
Exit For
End If
Next
End Function

Rick