Checking if workbook is open

  • Thread starter Thread starter Jesse
  • Start date Start date
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
 
Hi Jesse,

Function IsWBopen(wb As String) As Boolean
On Error Resume Next
IsWBopen = Len(Workbooks(wb).Name) > 0
End Function

Sub test()
MsgBox IsWBopen("funcres.xla")'your workbook name
End Sub


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)
 
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)
 
For Each C In Workbooks()
If C.Name = "Book1" Then MsgBox "Workbook " & C.Name & "
is open ...", vbInformation
Next C
 
Bob, this worked better than I expected and saved me tons of time. Thanks
for your help.

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

Jesse
 
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)
 
Back
Top