Workbooks, subscript out of range

S

Sysygy

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?
 
N

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?
 
D

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.
 
S

Sysygy

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

Sysygy

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

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.
 
S

Sysygy

Brilliant. Thanks again Dave.

Dave Peterson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top