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