Checking if workbook is open

J

Jesse

I'm sure I've seen this in the group before but I can't find it.

How would I check to see if a specific workbook is already open?

Jesse
 
B

Bob Phillips

Jesse,

A couple of alternatives

Function IsFileOpen(fName) As Boolean
On Error Resume Next
IsFileOpen = Len(Workbooks(fName).Name)
On Error Goto 0
End Function

and is invoked with a simple

IsFileOpen("Misc.xls")

or

Function GetFile(fName) As Workbook
On Error Resume Next
Set GetFile = Workbooks(fName)
On Error GoTo 0
End Function

and is used in this type of way

sFile = "Premiership 2003.xls"
If GetFile(sFile) Is Nothing Then
MsgBox sFile & " file needs to be open"
Else
MsgBox sFile & " file is already open"
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Rick

For Each C In Workbooks()
If C.Name = "Book1" Then MsgBox "Workbook " & C.Name & "
is open ...", vbInformation
Next C
 
J

Jesse

Bob, this worked better than I expected and saved me tons of time. Thanks
for your help.

Jesse
 
J

Jesse

Bob, this worked better than I expected and saved me tons of time. Thanks
for your help.

Jesse
 
B

Bob Phillips

Jesse,

Just read your response, so thanks for that, and glad to help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top