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.

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" wrote in message
news:cF1Jb.93487$VB2.211765@attbi_s51...
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.

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

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" wrote in message
news:l62Jb.253107$_M.1157754@attbi_s54...
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

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

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.

--
Regards,
Tom Ogilvy


Paul James wrote in message
news:sA2Jb.189096$8y1.606358@attbi_s52...
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

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.


"Paul James" wrote in message
news:sA2Jb.189096$8y1.606358@attbi_s52...
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,

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

Rob

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


Paul James wrote in message
news:sA2Jb.189096$8y1.606358@attbi_s52...
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

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