I want to test if a workbook is open. I've found these suggestions, but none of them work for me. Any ideas why
Thank
Ro

'I've tried
Function IsOpen(BookName As String) As Boolea
On Error Resume Nex
Set wb = Workbooks(BookName
IsOpen = Not wb Is Nothin
End Functio

'and
Function IsOpen(BookName As String) As Boolea
On Error Resume Nex
IsOpen = Not (Application.Workbooks(BookName) Is Nothing
End Functio

'and
Function IsOpen(BookName As String) As Boolea
On Error Resume Nex
IsOpen = Len(Workbooks(BookName).Name
End Functio

All three of the IsOpen functions work for me. Note that if the
workbook you are testing for has been saved, you must include the
".xls" file extension in the BookName. For example,

If IsOpen("Book1.xls") = True Then ' note the ".xls"

will always work if the workbook has been saved, but

If IsOpen("Book1") = True Then ' note the missing ".xls"

may not work if the workbook has been saved. It depends on the
Windows Explorer "Hide extensions for known file types" setting.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Rob" wrote in message
...
I want to test if a workbook is open. I've found these
suggestions, but none of them work for me. Any ideas why?
Thanks
Rob

'I've tried:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing
End Function

'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Not (Application.Workbooks(BookName) Is Nothing)
End Function

'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Len(Workbooks(BookName).Name)
End Function

What does "none of them work for me" mean?

--

Vasant

"Rob" wrote in message
...
I want to test if a workbook is open. I've found these suggestions, but
none of them work for me. Any ideas why?
Thanks
Rob

'I've tried:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing
End Function

'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Not (Application.Workbooks(BookName) Is Nothing)
End Function

'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Len(Workbooks(BookName).Name)
End Function

When I step through (or run the code of the function), I still get the error message on the second line below, where I expect it to just continue onto the next line
On Error Resume Nex
Set wb = Workbooks(BookName
IsOpen = Not wb Is Nothin


----- Vasant Nanavati wrote: ----

What does "none of them work for me" mean

--

Vasan

"Rob" wrote in messag
..
I want to test if a workbook is open. I've found these suggestions, bu
none of them work for me. Any ideas why
Thank
Ro
'I've tried
Function IsOpen(BookName As String) As Boolea
On Error Resume Nex
Set wb = Workbooks(BookName
IsOpen = Not wb Is Nothin
End Functio
'and
Function IsOpen(BookName As String) As Boolea
On Error Resume Nex
IsOpen = Not (Application.Workbooks(BookName) Is Nothing
End Functio
'and
Function IsOpen(BookName As String) As Boolea
On Error Resume Nex
IsOpen = Len(Workbooks(BookName).Name
End Functio

OK, let's take it step by step. *What* error message do you get?

For the example you gave, have you declared the variable wb?

--

Vasant

"Rob" wrote in message
...
When I step through (or run the code of the function), I still get the
error message on the second line below, where I expect it to just continue
onto the next line.
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing


----- Vasant Nanavati wrote: -----

What does "none of them work for me" mean?

--

Vasant

"Rob" wrote in message
...
I want to test if a workbook is open. I've found these
suggestions, but
none of them work for me. Any ideas why?
Thanks
Rob
'I've tried:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing
End Function
'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Not (Application.Workbooks(BookName) Is Nothing)
End Function
'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Len(Workbooks(BookName).Name)
End Function

If "On Error Resume Next" is being ignored:

In the VBE: ToolsOptionsGeneral. Make sure you have "Break on Unhandled
errors" set, not "Break on all errors".

--
George Nicholson

Remove 'Junk' from return address.


"Rob" wrote in message
...
When I step through (or run the code of the function), I still get the
error message on the second line below, where I expect it to just continue
onto the next line.
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing


----- Vasant Nanavati wrote: -----

What does "none of them work for me" mean?

--

Vasant

"Rob" wrote in message
...
I want to test if a workbook is open. I've found these
suggestions, but
none of them work for me. Any ideas why?
Thanks
Rob
'I've tried:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing
End Function
'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Not (Application.Workbooks(BookName) Is Nothing)
End Function
'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Len(Workbooks(BookName).Name)
End Function

Good point, George; I didn't even think about that.

--

Vasant

"George Nicholson" wrote in message
...
If "On Error Resume Next" is being ignored:

In the VBE: ToolsOptionsGeneral. Make sure you have "Break on Unhandled
errors" set, not "Break on all errors".

--
George Nicholson

Remove 'Junk' from return address.


"Rob" wrote in message
...
When I step through (or run the code of the function), I still get the
error message on the second line below, where I expect it to just continue
onto the next line.
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing


----- Vasant Nanavati wrote: -----

What does "none of them work for me" mean?

--

Vasant

"Rob" wrote in message
...
I want to test if a workbook is open. I've found these
suggestions, but
none of them work for me. Any ideas why?
Thanks
Rob
'I've tried:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
Set wb = Workbooks(BookName)
IsOpen = Not wb Is Nothing
End Function
'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Not (Application.Workbooks(BookName) Is Nothing)
End Function
'and:
Function IsOpen(BookName As String) As Boolean
On Error Resume Next
IsOpen = Len(Workbooks(BookName).Name)
End Function