Detecting if a Window/Workbook is Open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was wondering how I would have a macro detect whether or not a workbook is
open.

This is what I tried.......
If Application.Windows("Book1.xls") = True Then

Obviously it doesn't work but does someone know what would?

Thanks Much.
R
 
How about something like this

Sub Test()
Dim myWB As Workbook
Dim myString As String

myString = "Book2"
Set myWB = Nothing
On Error Resume Next
Set myWB = Workbooks(myString)
On Error GoTo 0
If Not myWB Is Nothing Then
Debug.Print myString & " is open"
Else
Debug.Print myString & " is not open"
End If

End Sub

HTH,
Barb Reinhardt
 
I like to use a function

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function


You can use this in your macro then

If bIsBookOpen("Book1.xls") Then
 
Try:-

Sub marine()
For Each wb In Workbooks
If wb.Name = ("mybook.xls") Then
MsgBox (wb.Name & " is open")
End If
Next wb
End Sub

Mike
 
They All Work for what I want to do... Now I just have to figure out which
one to use.

THANK YOU ALL!!!

R
 
Hi Rob,

Good Point! Sometimes people like you have more luck than others !

regards,
driller
 
Back
Top