Is Workbook open without error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arne Hegefors - 22 Nov 2007 13:45 GMT

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!

Reply to this Message

Bob Phillips - 22 Nov 2007 14:28 GMT

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

Signature

HTH

Bob

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

> Hi! I have a macro that sometimes opens another workbook. However
> sometimes
[quoted text clipped - 18 lines]
>
> Any help appreciated!

Reply to this Message

Arne Hegefors - 22 Nov 2007 14:53 GMT

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:

> You should pass just the workbook name to that routine, not the path as
> well.
[quoted text clipped - 21 lines]
> >
> > Any help appreciated!

Reply to this Message

Nigel - 22 Nov 2007 15:08 GMT

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

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

> Hi Bob! Thanks for your answer! I dont pass the path, although it might
> look
[quoted text clipped - 44 lines]
>> >
>> > Any help appreciated!

Reply to this Message