Identifying an open workbook / sheet

[Guest]

My process fails - error 9: subscript out of range at this statement:-
With Workbooks(Cranafile).Worksheets(SheetName)
on one PC! On all other PC's the macro works fine. The workbook defined by
cranafile is definately open and definately has a sheet called sheetname. On
my PC I can have the string defined in any case (upper or lower) and all is
OK with or without the .xls extension. On this maveric PC I've had to add
code to ensure the book and sheet names are exactly as held in
workbooks(<bookid>).name to make the with or any subsequent code find the
book and sheet. Why is this ? The maveric PC in on windows NT (I think) and
has recently had Excel re-installed. Is there a known bug in Excel here or in
there some parameter I need to set ? This was a real pain to find and code
round so any advice would be appreciated. Thanks

 

[Dave Peterson]

I think it's more of a windows/code problem than excel.

There's a windows setting that hides/displays file extensions. If you don't
specify the extension, then you code could fail (depending on this setting).

If you include the extension, then it'll work all the time--no matter what the
setting.

so instead of:
cranafile = "book1"
use
cranafile = "book1.xls"

(well, that's my guess!)

 

[Guest]

Thanks Dave - sounds like this is what was happening. I'll have a hunt round
for the windows setting to see if it makes the difference.

 

[Dave Peterson]

In win98, I'd do this:

Start windows explorer
View Tab|Uncheck the "hide file extensions for known file types"