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("Combat").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

From the VBE menu:

Tools | Options | General | Error Trapping.

Perhaps "Break on All Errors" is selected?

--

Vasant



"John Keith" <[email protected]> wrote in message
news:[email protected]...
> 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("Combat").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

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("Combat").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("Combat").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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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("Combat").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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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("Combat").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
> >
> >
> >

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("Combat").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