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