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

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

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.

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.