Workbooks, subscript out of range

[Guest]

I've written a program that works fine on my laptop but when I try to run it
on my desktop I get a subscript out of range error when the code tries to
activate a workbook, see below:

Workbooks("xyz").Worksheets("Log").Activate

In order to overcome the problem on the desktop I have to re-write the code
as follows:

Workbooks("xyz.xls").Worksheets("Log").Activate

I'm using xl2003 with identical settings. The desktop is running XP SP2 Home
and the laptop is running XP SP1 Prof.

Does anyone have any ideas why this happens?

 

[Nigel]

When a new book is opened it takes the name e.g. Book1 so the reference in
the workbooks collection is stored as Book1; when the book is saved, the
workbook takes the name including the file extension; so Book1 becomes
Book1.xls (if saved as a XL workbook).

You see this happening in the application caption and in the VBE with the
project name changing.

I suspect it is not the difference between the Desktop / Laptop, but whether
the file has been saved yet?

 

[Dave Peterson]

There's a windows setting that you can change to make sure that all extensions
are shown (like in windows explorer).

If you always include the extension in your code, then your code won't fail. It
won't matter what the user's setting is.

 

[Guest]

Many thanks for your reply. I thought it would be a switch buried deep within
Windows!

 

[Guest]

Many thanks for your reply. Unfortunately it's not the solution as I do save
the files, but thanks anyway.

 

[Dave Peterson]

It's not buried too deep:

Open windows explorer
tools|folder options|view tab
scroll down to the "hide extensions for known file types"
uncheck it.

But if you have lots of pc's to change, it will be a pain. It's much
simpler/safer to just always include that extension in your code.

 

[Guest]

Brilliant. Thanks again Dave.

It's not buried too deep:

Open windows explorer
tools|folder options|view tab
scroll down to the "hide extensions for known file types"
uncheck it.

But if you have lots of pc's to change, it will be a pain. It's much
simpler/safer to just always include that extension in your code.