Subscript out of range

[Bruce001]

I am running into a Run-time error '9', Subscript out of range. When I
run my macro in Excel 2000 and 2002, it works fine. When I run it under
Excel 2003, that is when I get this error. I open a text file and try to
move the values into sheets spread out throughout my workbook. Here is
what some of my code looks like:

ESTemp = ActiveWorkbook.Name
StringLength = Len(ESTemp)
EST = Left(ESTemp, StringLength - 4)

UF = Application.GetOpenFilename(FileFilter:="Text Files
(*.txt),*.txt", Title:="Open Estimator saved inputs file")
If UF = "False" Then
' user hit cancel'
Exit Sub
End If

Workbooks.OpenText Filename:=UF

ActiveSheet.Name = "LEst"

ESTemp2 = ActiveWorkbook.Name
StringLength = Len(ESTemp2)
ESTTEXT = Left(ESTemp2, StringLength - 4)

Workbooks(EST).Worksheets("HDQ1").Range("B10").Value =
Workbooks(ESTTEXT).Worksheets("LEst").Range("A90")

The macro dies when I try to move data from the input text file into my
worksheets. I change the name of the input text file sheet name because
when the users create the files, they can name them whatever they want.
I just don't understand why this works with older versions (9.0 & 10.0)
and not 11.0?

Bruce Gold

 

[Tom Ogilvy]

when referencing a workbook, you should not remove the extension as you show
you are doing in the code. Whether it works without the extension is based
on a windows setting. However, it always works when you use the extension
regardless of the setting, so it is best always use the extension when
refering to a workbook.

Workbooks("Myworkbook.xls") will always work
Workbooks("Myworkbook") will only work sometimes.

 

[Bruce001]

Thanks Tom. That seems to have done the trick. I'm not even sure why I
stripped off the extension before. It is old code that broke when
applied to Excel 2003.