Resume Next doesn't work in IsOpen function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
I want to test if a workbook is open. I've found these
Click to expand...
suggestions, but none of them work for me. Any ideas why?
 
What does "none of them work for me" mean?

--

Vasant

Rob said:
I want to test if a workbook is open. I've found these suggestions, but
Click to expand...
none of them work for me. Any ideas why?
 
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
Click to expand...


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

What does "none of them work for me" mean

--

Vasan

Rob said:
I want to test if a workbook is open. I've found these suggestions, bu
Click to expand...
none of them work for me. Any ideas why
 
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 said:
When I step through (or run the code of the function), I still get the
Click to expand...
error message on the second line below, where I expect it to just continue
onto the next line.
 
If "On Error Resume Next" is being ignored:

In the VBE: Tools>Options>General. Make sure you have "Break on Unhandled
errors" set, not "Break on all errors".

--
George Nicholson

Remove 'Junk' from return address.


Rob said:
When I step through (or run the code of the function), I still get the
Click to expand...
error message on the second line below, where I expect it to just continue
onto the next line.
 
Back
Top