Checking for open workbook...

  • Thread starter Thread starter Squid
  • Start date Start date
S

Squid

I want to add some code to a workbook_BeforeClose event.
For example sake, lets call this Workbook1. I want to
sort the contents of Workbook1!Sheet1 only if Workbook2
is not open.

How can I test to see if another Workbook is open?

Thanks
Mike
 
Hi Mike
try the following

Dim WBook As Workbook
'...
On Error Resume Next
Set WBook = Workbooks("Workbook2")
On Error GoTo 0
If WBook Is Nothing Then
'your sorting code
End If
'...
 
Mike,

Here is a simple function to check

Function IsOpen(FileName As String) As Boolean
Dim oWB As Workbook
On Error Resume Next
Set oWB = Workbooks(FileName)
IsOpen = Not oWB Is Nothing
End Function

Use like this

Debug.Print IsOpen("SIP 2004.xls")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Or even shorter ;-):

Function IsOpen(FileName As String) As Boolean
On Error Resume Next
IsOpen = CBool(Len(Workbooks(FileName).Name))
End Function

It even works without the CBool but I don't like ot use implicit coercion.
 
Hi Bob:

I'm sure I stole it from someone else ... probably Dana DeLouis <g>.

Regards,

Vasant.
 
Or even shorter ;-):

Function IsOpen(FileName As String) As Boolean
On Error Resume Next
IsOpen = CBool(Len(Workbooks(FileName).Name))
End Function
Click to expand...
...

If you want shorter, you might as well go for fewer function calls too.

Function IsFile(fn As String) As Boolean
On Error Resume Next
IsFile = (Workbooks(fn).Name <> "")
End Function
 
Hi Harlan:

You had made a similar "enhancement" the last time I posted this solution,
but I couldn't remember what it was. Thanks!

Regards,

Vasant.
 
Back
Top