Checking for open workbook...
 

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

Checking for open workbook...
 

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
'...



--
Regards
Frank Kabel
Frankfurt, Germany

Squid wrote:
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

Checking for open workbook...
 

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)

"Squid" wrote in message
...
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

Checking for open workbook...
 

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.

--

Vasant


"Bob Phillips" wrote in message
...
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)

"Squid" wrote in message
...
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

Checking for open workbook...
 

Always got to go one better<vbg.

I think I'll steal that.

Bob

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant


"Bob Phillips" wrote in message
...
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)

"Squid" wrote in message
...
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

Checking for open workbook...
 

Hi Bob:

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

Regards,

Vasant.


"Bob Phillips" wrote in message
...
Always got to go one better<vbg.

I think I'll steal that.

Bob

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant


"Bob Phillips" wrote in message
...
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)

"Squid" wrote in message
...
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

Checking for open workbook...
 

"Vasant Nanavati" wrote...
Or even shorter ;-):

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

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

--
To top-post is human, to bottom-post and snip is sublime.

Checking for open workbook...
 

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.

"Harlan Grove" wrote in message
...
"Vasant Nanavati" wrote...
Or even shorter ;-):

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

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

--
To top-post is human, to bottom-post and snip is sublime.