Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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" wrote in message ... | 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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
VBA code error with Protection turned on - help please | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Code to select range | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) |