workbooks.open and error handling
 

Im getting an error when running this code on the Set. (I found this code as
a solution on another post, but it won't seem to work.)

thedir = CurDir()
On Error GoTo notOpen
Set xlTest = Workbooks("ThePlayingboard.xls") ***
GoTo itsOpenNow
notOpen:
Workbooks.Open Filename:=theDir & "\" & "ThePlayingboard.xls",
UpdateLinks:=0
itsOpenNow:
Workbooks("ThePlayingboard.xls").Worksheets("Comba t").Activate

At the *** marker, this is the line that gets a "runtime error 9, subscript
out of range" which I assume is the Workbooks(sub) its talking about. and
this Should happen because the file was not open. I was under the impression
that the On Error GoTo label would trap the error and force the execution
pointer to jump on past the error.

I'm just trying to make sure the file is open, if it is open then Activate,
else open it then activate. How do you do that?

--
Regards,
John

workbooks.open and error handling
 

From the VBE menu:

Tools | Options | General | Error Trapping.

Perhaps "Break on All Errors" is selected?

--

Vasant



"John Keith" wrote in message
...
Im getting an error when running this code on the Set. (I found this code
as
a solution on another post, but it won't seem to work.)

thedir = CurDir()
On Error GoTo notOpen
Set xlTest = Workbooks("ThePlayingboard.xls") ***
GoTo itsOpenNow
notOpen:
Workbooks.Open Filename:=theDir & "\" & "ThePlayingboard.xls",
UpdateLinks:=0
itsOpenNow:
Workbooks("ThePlayingboard.xls").Worksheets("Comba t").Activate

At the *** marker, this is the line that gets a "runtime error 9,
subscript
out of range" which I assume is the Workbooks(sub) its talking about. and
this Should happen because the file was not open. I was under the
impression
that the On Error GoTo label would trap the error and force the execution
pointer to jump on past the error.

I'm just trying to make sure the file is open, if it is open then
Activate,
else open it then activate. How do you do that?

--
Regards,
John

workbooks.open and error handling
 

I find this more straightforward:

dim xlTest as workbook

set xltest = nothing
on error resume next
set xltest = workbooks("theplayingboard.xls")
on error goto 0

if xltest is nothing then
set xltest = workbooks.open(....)
end if

xltest.activate
xltest.worksheets("combat").activate

===


I think this'll cause trouble if ThePlayingBoard.xls isn't active:
Workbooks("ThePlayingboard.xls").Worksheets("Comba t").Activate



John Keith wrote:

Im getting an error when running this code on the Set. (I found this code as
a solution on another post, but it won't seem to work.)

thedir = CurDir()
On Error GoTo notOpen
Set xlTest = Workbooks("ThePlayingboard.xls") ***
GoTo itsOpenNow
notOpen:
Workbooks.Open Filename:=theDir & "\" & "ThePlayingboard.xls",
UpdateLinks:=0
itsOpenNow:
Workbooks("ThePlayingboard.xls").Worksheets("Comba t").Activate

At the *** marker, this is the line that gets a "runtime error 9, subscript
out of range" which I assume is the Workbooks(sub) its talking about. and
this Should happen because the file was not open. I was under the impression
that the On Error GoTo label would trap the error and force the execution
pointer to jump on past the error.

I'm just trying to make sure the file is open, if it is open then Activate,
else open it then activate. How do you do that?

--
Regards,
John

--

Dave Peterson

workbooks.open and error handling
 

That was a great thought, but it wasn't the case. "Break on unhandled
errors" was selected.

I have tried the same code on Excel 2003 and 2002. The same problem exists
that the On Error goto... is not catching the run time error 9; Subscript
out of range on the workbooks.open

Or am i misunderstanding how the "On Error" works? (im still new to VBA)

--
Regards,
John


"Vasant Nanavati" wrote:

From the VBE menu:

Tools | Options | General | Error Trapping.

Perhaps "Break on All Errors" is selected?

--

Vasant



"John Keith" wrote in message
...
Im getting an error when running this code on the Set. (I found this code
as
a solution on another post, but it won't seem to work.)

thedir = CurDir()
On Error GoTo notOpen
Set xlTest = Workbooks("ThePlayingboard.xls") ***
GoTo itsOpenNow
notOpen:
Workbooks.Open Filename:=theDir & "\" & "ThePlayingboard.xls",
UpdateLinks:=0
itsOpenNow:
Workbooks("ThePlayingboard.xls").Worksheets("Comba t").Activate

At the *** marker, this is the line that gets a "runtime error 9,
subscript
out of range" which I assume is the Workbooks(sub) its talking about. and
this Should happen because the file was not open. I was under the
impression
that the On Error GoTo label would trap the error and force the execution
pointer to jump on past the error.

I'm just trying to make sure the file is open, if it is open then
Activate,
else open it then activate. How do you do that?

--
Regards,
John

workbooks.open and error handling
 

I tried your code on Excel 2002 and it worked just fine. The error was
handled as desired.

Do you have any error handling statements (that are not reset to 0) in an
earlier part of your code, or possibly in another sub that is calling this
sub?

--

Vasant


"John Keith" wrote in message
...
That was a great thought, but it wasn't the case. "Break on unhandled
errors" was selected.

I have tried the same code on Excel 2003 and 2002. The same problem
exists
that the On Error goto... is not catching the run time error 9; Subscript
out of range on the workbooks.open

Or am i misunderstanding how the "On Error" works? (im still new to VBA)

--
Regards,
John


"Vasant Nanavati" wrote:

From the VBE menu:

Tools | Options | General | Error Trapping.

Perhaps "Break on All Errors" is selected?

--

Vasant



"John Keith" wrote in message
...
Im getting an error when running this code on the Set. (I found this
code
as
a solution on another post, but it won't seem to work.)

thedir = CurDir()
On Error GoTo notOpen
Set xlTest = Workbooks("ThePlayingboard.xls") ***
GoTo itsOpenNow
notOpen:
Workbooks.Open Filename:=theDir & "\" & "ThePlayingboard.xls",
UpdateLinks:=0
itsOpenNow:
Workbooks("ThePlayingboard.xls").Worksheets("Comba t").Activate

At the *** marker, this is the line that gets a "runtime error 9,
subscript
out of range" which I assume is the Workbooks(sub) its talking about.
and
this Should happen because the file was not open. I was under the
impression
that the On Error GoTo label would trap the error and force the
execution
pointer to jump on past the error.

I'm just trying to make sure the file is open, if it is open then
Activate,
else open it then activate. How do you do that?

--
Regards,
John

workbooks.open and error handling
 

Hi John,

Firstly the problem with your code.
Workbooks("Filename.xls") works only if the workbook is part of the
workbooks collection ie if it is already open. Since at the point of
invocation, it is not open, it will give an error. You may set a workbook
variable to it after opening the file without error.

What you need to do is to
1. Determine if the file is existing in the given path. Use the Dir
function
dim Fil as String
Fil = dir(path &"\" filename.xls") will return an empty string if
the file is not in the given path, else it will return the filename. You may
use wild cards on Windows - apparantly does not work on Macs.

2. Determine if the workbook is already open. To do so, you have to
search the workbooks collection

Dim wb as workbook, isOpen as Boolean
IsOpen = False
for each wb in workbooks
if wb.name = "xyz.xls" then
IsOpen = True
exit For
endif
end for
[use IsOpen logical variable at this point to check]



"John Keith" wrote:

Im getting an error when running this code on the Set. (I found this code as
a solution on another post, but it won't seem to work.)

thedir = CurDir()
On Error GoTo notOpen
Set xlTest = Workbooks("ThePlayingboard.xls") ***
GoTo itsOpenNow
notOpen:
Workbooks.Open Filename:=theDir & "\" & "ThePlayingboard.xls",
UpdateLinks:=0
itsOpenNow:
Workbooks("ThePlayingboard.xls").Worksheets("Comba t").Activate

At the *** marker, this is the line that gets a "runtime error 9, subscript
out of range" which I assume is the Workbooks(sub) its talking about. and
this Should happen because the file was not open. I was under the impression
that the On Error GoTo label would trap the error and force the execution
pointer to jump on past the error.

I'm just trying to make sure the file is open, if it is open then Activate,
else open it then activate. How do you do that?

--
Regards,
John