Workbook and Window object question

[Frederick Chow]

Hi all,

I have a question about specifying a member of the workbooks and windows
collection.

Suppose I have opened a file called "ABCDE.XLS", I found that whether I
should specify it as Workbooks("abcde.xls") or just Workbooks("abcde")
depends on a windows folder setting "Hide extensions for known file types".
That makes a problem for me as my macros will be run on someone else's
computers.

Are there any possible means to make things easier, for example is it
possible to use wildcards to specify a member of a workbooks or windows
collection? Please advise.

Frederick Chow
Hong Kong.

 

[Doug Glancy]

Frederick,

If you use the file extension, e.g., Workbooks(''tester.xls") it will work
for either Windows setting.

hth,

Doug

 

[Frederick Chow]

I know, but I can't be sure if my macro will work properly if someone else's
PC doesn't use extensions. So any further advise?

Frederick Chow

 

[Tom Ogilvy]

All PC's support extensions. the option you talk about just doesn't display
the extension. It doesn't change the way Excel saves the file.

If you are querying the user for the filename (and you thing they may not
enter the extension), then you can check for the existence of the file with
the Dir command. the dir command does support wildcards. Once you
ascertain the existence of the file and its exact name, you can proceed.
Use the exact name of the file (including extension if it has one) and you
should do fine.

 

[Frederick Chow]

Oh, I didn't mean that.

Try it:

1. Suppose you have a workbook called "ABCDE.xls".
2. Make sure that the folder setting "Hide extensions for known file types"
is turned OFF
3. In immediate window type "workbooks("ABCDE.xls").name" -> no error
4. Turn ON the folder setting "Hide extensions for know file types".
5. Re-run the same command "workbooks("ABCDE.xls").name -> ERROR
6. Run a similar command "workbooks("ABCDE").name -> OK

Frederick Chow

 

[Tom Ogilvy]

I can't reproduce that error. It always works if you use the full name.

without "Hide extensions for known file types" (not selected)

? workbooks("abcdef").name '<== error
? workbooks("abcdef.xls").Name
abcdef.xls

With Hide extensions for known file types" (selected)
? workbooks("abcdef").name
abcdef.xls
? workbooks("abcdef.xls").Name
abcdef.xls

You already agreed that you know that:

Glancy:

If you use the file extension, e.g., Workbooks(''tester.xls") it will work
for either Windows setting. Your Response
I know, . . .

So now two people have told you. If your getting a different result, it may
be a regional issue.

 

[Frederick Chow]

Tom,

Thank you very much anyway for your effort.

Frederick Chow