Find: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.

--
Regards,
Tom Ogilvy


"Bruce001" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile:
http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=490173
>

[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.