By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

Calling excel: subscript out of range

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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".
Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.