Is Workbook open without error

[Arne Hegefors]

Hi! I have a macro that sometimes opens another workbook. However sometimes
that workbook is already open. In that case I do not want to reopen it. I
have a UDF that checks if the workbook in fact is open but the problem is
that I get Error 9. Is there any way to check if the workbook is open and not
get an error? (this error ruins the rest of my program..). strFXOptionPath is
a string that contains the name of the workbook.

If WorkbookIsOpen(strFXOptionPath) = False Then
........

Private Function WorkbookIsOpen(wbname) As Boolean
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else: WorkbookIsOpen = False
End Function


Any help appreciated!

 

[Bob Phillips]

You should pass just the workbook name to that routine, not the path as
well.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arne Hegefors]

Hi Bob! Thanks for your answer! I dont pass the path, although it might look
like that. Like I said strFXOptionPath is a string the contains the NAME of
the workbook not the path. However that has nothing to do with the error. The
error comes from:
Set x = Workbooks(wbname). If Err=0 then it is open and if Err<>0 then it
is not open. As the code suggest that line produces an error. Now this error
is not a problem at that particular place but it gets to be a problem later.
therefore i wonder if there is any way of checking if the book is open
without producing an error in case it is not open. Would very much appreciate
help with this! Thanks alot!

"Bob Phillips" skrev:

 

[Nigel]

Should you disable error handling in the function ?

Private Function WorkbookIsOpen(wbname) As Boolean
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else: WorkbookIsOpen = False

On Error GoTo 0

End Function


--

Regards,
Nigel
(e-mail address removed)