Calling excel: subscript out of range

[EJO]

In my app (A2k), I have the following code, which opens the file, but
also brings up a pop-up which states the "subscript is out of range":

Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim SRval, OLDir, Ver, filePath, fileList, CountVer

Const OLInfo = "OL Template"
Set objXL = GetObject("", "Excel.Application")

SRval = Left(Me!SR, Len(Me!SR) - 4)
OLDir = "\\oflnmoejfs1\shares\eol\" & SRval & "0000-" & SRval &
"9999\"
fileList = OLDir & Me!SR & "V?.xls"
CountVer = Dir(fileList)

Do Until CountVer = ""
Ver = Ver + 1
CountVer = Dir()
Loop

filePath = OLDir & Me!SR & "V" & Ver & ".xls"
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(filePath)
Set objSht = objWkb.Worksheets(OLInfo)
End With
It is worthy to note that this is also used in another place, which
results without opening a file, but oddly, no error (the file exists).
I believe the problem is with the Do Until Loop. I changed this code
from calling excel.exe directly instead of the api(?), and that is when
the problems popped up. Previously, I was calling XL and the file as
you would on a command line. All my references are still intact.
Tryin to learn--really!

Thanks for the help!

Eric

[corey lawson]

"EJO" <5p****@centurytel.net> wrote in news:1118421980.669751.147380
@g43g2000cwa.googlegroups.com:

In my app (A2k), I have the following code, which opens the file, but
also brings up a pop-up which states the "subscript is out of range":

Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim SRval, OLDir, Ver, filePath, fileList, CountVer

Const OLInfo = "OL Template"
Set objXL = GetObject("", "Excel.Application")

SRval = Left(Me!SR, Len(Me!SR) - 4)
OLDir = "\\oflnmoejfs1\shares\eol\" & SRval & "0000-" & SRval &
"9999\"
fileList = OLDir & Me!SR & "V?.xls"
CountVer = Dir(fileList)

Do Until CountVer = ""
Ver = Ver + 1
CountVer = Dir()
Loop

filePath = OLDir & Me!SR & "V" & Ver & ".xls"
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(filePath)
Set objSht = objWkb.Worksheets(OLInfo)
End With
It is worthy to note that this is also used in another place, which
results without opening a file, but oddly, no error (the file exists).
I believe the problem is with the Do Until Loop. I changed this code
from calling excel.exe directly instead of the api(?), and that is when
the problems popped up. Previously, I was calling XL and the file as
you would on a command line. All my references are still intact.
Tryin to learn--really!

Thanks for the help!

Eric

The workbook you are trying to open that invokes the error doesn't have a
sheet named "OL Template".

[EJO]

Thanks, Corey. No, the only thing that has changed is the means by
which excel is being called. To be sure, I checked, and the excel file
still has a sheet tab "OL Template". I eventually got this to
work--don't know how, though, but am thinking I had a typo that i
unknowingly corrected.