Detecting if a Window/Workbook is Open

R

Rob

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
 
B

Barb Reinhardt

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
 
R

Ron de Bruin

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
 
M

Mike H

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
 
R

Rob

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

THANK YOU ALL!!!

R
 
D

driller

Hi Rob,

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

regards,
driller
 
Top