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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '9'
I'd check to see if there was a different typo error. I've always included the
extension and I've never seen it fail (too). And has that workbook that you're testing been saved--so that it has an extension (and is that extension .xls)??? John Bundy wrote: Thanks for the info Chip, I wouldn't have figured that Hide Extesions would matter. But, I get the error if I use .xls but you state "It is always safe use the "xls" file extension", why is this not working for us? -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Chip Pearson" wrote: "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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '9'
Hi ,
How is everything going? Please feel free to let me know if you need any assistance. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '9'
When I try running my recorded Macros's is get an error mesage saying "run
time error 9" I have tried all the suggestet solutions below but none of them have work. Do you have any other recommendations for me. Thanks, Manny -- shagoro "Wei Lu [MSFT]" wrote: Hi , How is everything going? Please feel free to let me know if you need any assistance. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
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 |