opening or activating another workbook file

[Paul James]

Happy New Year to everyone!

I'm trying to create a procedure that does the following:

1. opens a workbook named "myFile.xls" and makes that newly opened workbook
the active workbook.
2. if that workbook is already open but is not the active workbook, then it
makes myFile.xls the active workbook.

I'd like to accomplish this without causing the user to click through alerts
or message boxes when the procedure runs.

In this application, myFile.xls always exists in the current directory, so
there's no need to use the dir command to change directories, or to specify
the directory path. Under these circumstances, I know you can open the file
using

Workbooks.Open Filename:="myFile.xls"

and if I knew it was already open, you could activate it using

Windows("myFile.xls").Activate

But how do I handle it if I don't know whether the file is alerady open, and
I need to make it the active workbook?

Thank you in advance.

 

[Rob van Gelder]

Paul,

Sub tesit()
Dim wkb As Workbook
On Error GoTo e
Set wkb = Workbooks("myFile.xls")
On Error GoTo 0
wkb.Activate

Exit Sub
e: Workbooks.Open "C:\T\myFile.xls"
Resume
End Sub

Rob

 

[Paul James]

Great - thanks, Rob.

Question: I understand what the code is doing except for the line

On Error GoTo 0

In the event of an error (when myFile.xls isn't already open) the procedure
is directed to "e:" to open the file, which is what we'd want it to do, so
why do we need "On Error GoTo 0?" What does that line do?

Thanks again,

Paul

 

[Rob van Gelder]

In the context of the code I gave you, nothing important.

Since I specified an error handler (ie. When there's an error goto e) I
might have wanted to turn it off after I had finished with it.
I can disable a custom error handler within that procedure by going On Error
GoTo 0.
There is no label called 0, it's a special way of telling Excel for it to
handle errors itself (within that procedure)

From VBA Help, do a search for "On Error"

Rob

 

[Paul James]

Ok, that makes sense. But if that's the case, wouldn't we want to put the
On Error GoTo 0 after

wkb.Activate

rather than before it, in case we encounter an error trying to activate it?

This is what would happen if myFile.xls wasn't already open, in which case
we'd want to go to the error handler in "e:".

Paul

 

[Tom Ogilvy]

If you wanted to do it that way

Sub ActivateBook()
On Error goto e
Workbooks("MyFile.xls").Activate
Exit Sub
e: Workbooks.Open "Myfile.xls"
End Sub

When a workbook is opened, it is the ActiveWorkbook.

 

[Rob van Gelder]

It could error on Activate for various reasons, but this time we don't want
the e error handler to deal with it. It actually goes unhandled by my code.
Set wkb = Workbooks("myFile.xls") will fail if the workbook "myFile.xls"
isn't already open.
e handles that error, opens the workbook, then "Resume" tries to set wkb
again - this time succeeding.

 

[Rob van Gelder]

Tom,

Thanks. Sometimes I don't see the shortcuts in front of my own eyes.

Rob

 

[Paul James]

Thank you, gentlemen, for solving my problem and for the additional
information on handling these situations.