Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '9'
"John Bundy" (remove) wrote in message
Try taking out the .xls, that worked for me. I need to check my old code because I remember using that too I thought. Be careful with that advice. It is possible that there is an unsaved workbook named "Book1" open along with a saved workbook "Book1.xls". For example, Debug.Print Workbooks("Book1").Worksheets(1).Range("A1").Value Debug.Print Workbooks("Book1.xls").Worksheets(1).Range("A1").V alue prints values from two separate workbooks. Even with a single workbook "Book1.xls" open, you may need the ".xls" extension in workbook name. If you have the Windows setting "Hide extension for known file types" CHECKED, the ".xls" is not required. The follow code will work fine. Debug.Print Workbooks("Book1").Worksheets(1).Range("A1") However, if you have "Hide extension for known file types" UNCHECKED, which is the way I keep my system, the ".xls" is required and code like the following will blow up even if you have "Book1.xls" open. Debug.Print Workbooks("Book1").Worksheets(1).Range("A1") ' Blows Up With Err 9 Debug.Print Workbooks("Book1.xls").Worksheets(1).Range("A1").V alue ' OK It is always safe use the "xls" file extension, regardless of the Window setting. Omitting the "xls" extension can cause problem depending on your Windows settings. Personally, I think it is one of the dumber things MS has done to make the workbooks collection behave based on this Windows setting, but they didn't ask me about it. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "John Bundy" (remove) wrote in message ... Try taking out the .xls, that worked for me. I need to check my old code because I remember using that too I thought. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Jason" wrote: I have some code that toggles between open workbooks. The code has worked fine up until the last couple of days. Now, when I try to activate a workbook within the code (see below), I get a message box with "Run-time error '9': Subscript out of range". Any ideas as to what is causing this and how to fix. Thx. Windows("Book1.xls").Activate Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |