error 9 - simple code

[PBcorn]

to open a workbook if it is not already open:

dim path as string

path = " letter:\...\\workbook.xls"

If Workbooks(path).IsOpen = False Then
workbooks.open filename:=path

Gives subscript out of range. The solutions i found by searching the forum
are a bit long-winded - why doesn't the above work?

 

[Sam Wilson]

It's because the workbooks collection contains only open workbooks.

You can try the following, which checks all the open workbooks against the
path you've specified:

Sub test()

Dim path As String
path = "C:\blah.xls"

Dim wb As Workbook
For Each wb In Workbooks
If wb.FullName = path Then Exit Sub
Next wb

Workbooks.Open Filename:=path

End Sub


You should be able to

 

[PBcorn]

This generates a type mismatch error 13. please advise.

 

[PBcorn]

....which seems to be due to the "if wb.fullname = path" line.

 

[Sam Wilson]

What version of excel are you using? It mustn't be treating wb.fullname as a
string...

try cstr(wb.fullname) instead? or wb.fullname.tostring

 

[PBcorn]

Excel 2002 SP3

cstr generates the same error, wb.fullname.tostring generates "invalid
qualifier".

Please advise.

 

[Sam Wilson]

Can you post your code here so I can have a look?

 

[PBcorn]

I omitted to mention that i had added an "or" as follows:

If wb.FullName = path2 or path3 Then Exit Sub

which seems to cause the problem. using two if statments is not a solution
as obviously the exit sub of the first negates the second.

I could just write two subs but is there a better way?

 

[Dave Peterson]

Dim myPath as string
dim myFileName as string
dim wkbk as workbook

mypath = "C:\something\"
if right(mypath,1) <> "\" then
mypath = mypath & "\"
end if

myfilename = "workbook.xls" '<-- don't include the path here!

set wkbk = nothing
on error resume next
set wkbk = workbooks(myfilename)
on error goto 0

if wkbk is nothing then
on error resume next
set wkbk = workbooks.open(filename:=mypath & myfilename)
on error goto 0
end if

if wkbk is nothing then
msgbox "Not open and can't find it!"
else
msgbox wkbk.fullname
end if



This assumes that C:\something\

 

[Sam Wilson]

Rather than "If wb.FullName = path2 or path3 Then Exit Sub"

use "If wb.FullName = path2 or wb.fullname = path3 Then Exit Sub"

 

[Dave Peterson]

I was going to say that "This assumes that C:\something\workbook.xls" exists.

Then I changed the code to try to open it and report back if it fails. Ignore
that line in the original post.