Subscript out of range ... sometimes! (Same code unpredictable re

XL 2003

The file Test.txt is in memory but I want it to be the active sheet
therefore I attempt to execute:

....
Dim UpdateFileName as String
....
UpdateFileName = "Test.xls" 'shown just as example
Windows(UpdateFileName).Activate
....

Sometimes I get "Subscript out of range" error other times not.

Results of major frustrating investigation:

I noted that when the filename only (i.e. Test.xls) in on an XL Tab on the
(Win XP) task bar, all is OK.

When the path is included with the filename on an XL Tab on the XP Task bar
I get the error.

Further, if I use the XL file open wizard - I do NOT get the error. That
said, if I load Test.xls by double clicking Test.xls from Windows Explorer
AND it is the first use in the day of that file on the startup of XL - the
path shows on the Tab AND I get the error.

Therefore, having the path on the Win XP Task bar Tab = error

I or we may never know why, but does anyone know how to change my code to
avoid the error?

Dennis

I got it!!

Thanks for all who may have invested any time!

Dennis

*******************************************
....
Dim UpdateFileName as String
....
UpdateFileName = "Test.xls" 'shown just as example
Windows(UpdateFileName).Activate
....

*******************************************
Became:


Dim UpdateFileName as String
....
DataFilePath = ActiveWorkbook.Path
....
UpdateFileName = "Test.xls" 'shown just as example
Set UpdateWorkBook = Nothing
On Error Resume Next
Set UpdateWorkBook = Workbooks(UpdateFileName)
On Error GoTo 0
If UpdateWorkBook Is Nothing Then
Set UpdateWorkBook = Workbooks.Open(fileName:=DataFilePath & "\" &
UpdateFileName)
End If
....

*******************************************

"Dennis" wrote:

> XL 2003
>
> The file Test.txt is in memory but I want it to be the active sheet
> therefore I attempt to execute:
>
> ...
> Dim UpdateFileName as String
> ...
> UpdateFileName = "Test.xls" 'shown just as example
> Windows(UpdateFileName).Activate
> ...
>
> Sometimes I get "Subscript out of range" error other times not.
>
> Results of major frustrating investigation:
>
> I noted that when the filename only (i.e. Test.xls) in on an XL Tab on the
> (Win XP) task bar, all is OK.
>
> When the path is included with the filename on an XL Tab on the XP Task bar
> I get the error.
>
> Further, if I use the XL file open wizard - I do NOT get the error. That
> said, if I load Test.xls by double clicking Test.xls from Windows Explorer
> AND it is the first use in the day of that file on the startup of XL - the
> path shows on the Tab AND I get the error.
>
> Therefore, having the path on the Win XP Task bar Tab = error
>
> I or we may never know why, but does anyone know how to change my code to
> avoid the error?
>
> Dennis

Unless you have multiple windows open for a given workbook I'd activate a
workbook via the Workbook object:

Workbooks("Book1.xls").Activate

Not that there's is anything wrong with what you're doing as long as the
window name you specify matches exactly a name that appears on the list of
windows at the bottom of Excel's Window menu.

--
Jim
"Dennis" <[email protected]> wrote in message
news:[email protected]...
| XL 2003
|
| The file Test.txt is in memory but I want it to be the active sheet
| therefore I attempt to execute:
|
| ...
| Dim UpdateFileName as String
| ...
| UpdateFileName = "Test.xls" 'shown just as example
| Windows(UpdateFileName).Activate
| ...
|
| Sometimes I get "Subscript out of range" error other times not.
|
| Results of major frustrating investigation:
|
| I noted that when the filename only (i.e. Test.xls) in on an XL Tab on the
| (Win XP) task bar, all is OK.
|
| When the path is included with the filename on an XL Tab on the XP Task
bar
| I get the error.
|
| Further, if I use the XL file open wizard - I do NOT get the error. That
| said, if I load Test.xls by double clicking Test.xls from Windows Explorer
| AND it is the first use in the day of that file on the startup of XL - the
| path shows on the Tab AND I get the error.
|
| Therefore, having the path on the Win XP Task bar Tab = error
|
| I or we may never know why, but does anyone know how to change my code to
| avoid the error?
|
| Dennis

There's a disconnect in your message.

You write test.txt at first, but change it to test.xls. I'm guessing that
test.xls is correct.

If you're opening that workbook within your code, you can use:

dim mywkbk as workbook
set mywkbk = workbooks.open(filename:="c:\test.xls")

Then instead of going through the windows collection, you can just use that
workbook variable:

mywkbk.activate

====
if you're really using test.txt, you can do this:

dim mywkbk as workbook
workbooks.opentext filename:="C:\test.txt", ...
set mywkbk = activeworkbook
....



Dennis wrote:
>
> XL 2003
>
> The file Test.txt is in memory but I want it to be the active sheet
> therefore I attempt to execute:
>
> ...
> Dim UpdateFileName as String
> ...
> UpdateFileName = "Test.xls" 'shown just as example
> Windows(UpdateFileName).Activate
> ...
>
> Sometimes I get "Subscript out of range" error other times not.
>
> Results of major frustrating investigation:
>
> I noted that when the filename only (i.e. Test.xls) in on an XL Tab on the
> (Win XP) task bar, all is OK.
>
> When the path is included with the filename on an XL Tab on the XP Task bar
> I get the error.
>
> Further, if I use the XL file open wizard - I do NOT get the error. That
> said, if I load Test.xls by double clicking Test.xls from Windows Explorer
> AND it is the first use in the day of that file on the startup of XL - the
> path shows on the Tab AND I get the error.
>
> Therefore, having the path on the Win XP Task bar Tab = error
>
> I or we may never know why, but does anyone know how to change my code to
> avoid the error?
>
> Dennis

--

Dave Peterson