Odd Workbook.Name result

[Ray]

Hello -

What would cause Workbook.Name to return an 'odd' result, partially
representing the file name, but not completely? Read on for more
details ...

My stores use a template to create daily Cash Reconciliation files,
which are then submitted automatically to a specified network drive.
Once a week, they need update a consolidation workbook, which uses a
procedure to open each available CashRec and pull out some data.

With all stores, this works find and has for many months -- all stores
use the same CashRec template and consolidation template ... they
simply enter their own store # to drive the submit and consolidate
procedures.

One store recently developed a problem whereby their consolidation
report won't update properly. Using the Watch window, I was able to
identify that when the consolidation procedure opens the daily files,
somehow the name changes just a little! When I open the same file
manually (using Windows Explorer), the name is fine ... for example,
if I open Store145's file for July 23:
should open as: 1452311.xls (workbook.name the same)
procedure opens as: 14523118 (no .xls at the end)

Why would this happen? As a test, I took the same consolidation
workbook and changed the store # ... it worked fine. So, it would
seem that the problem is with the daily files ... but NO other stores
are having an issue. Has some property been changed or ???

Please help ...

Thanks, ray

 

[Chip Pearson]

This is most likely due to a seemingly unrelated Windows Explorer
setting. There is a setting called "Hide Extensions For Known File
Types", which hides the extension of a file name if that extension is
registered with Windows in the HKEY_CLASSES_ROOT region of the
Registry. Thus, a file like "Book1.xls" would be displayed as just
"Book1", without the ".xls". The reason MS put in this option is that
many users don't know what a file extension is and why it is
important, so when renaming a file, they botch the extension and get a
nasty message from Windows and, possibly, make it impossible to open
the file by double-clicking its icon in a folder window.

BUT.......

This is a open invitation to cause mayhem on an unsuspecting user's
computer. You could put a file named "Party.jpg.exe", an executable
file, on a user's computer, and if the "Hide Extensions" property is
set, that file name will be displayed as "Party.jpg", without the exe
extension. Most users know that jpg files are harmless and don't make
the connection between the "Hide Extensions" property being set and
the fact that they are in fact seeing an extension. So, thinking it is
a simple jpg image, they double-click the file icon expecting to see a
jpg picture of a party, but they actually launch an exe that embarks
on a massive search and destroy mission.

Excel uses this setting (a bad design decision, in my opinion) when
accessing workbook names in the Workbooks collection. Say you have a
workbook named "Book1.xls". If the "Hide Extensions" setting is
enabled to hide extensions, the following line of code will fail:

Debug.Print Workbooks("Book1").Name

because with extension hidden, there is no workbook named "Book1" . If
the "Hide Extensions" is enabled, that line of code will work just
fine. You can also get around this by always including the file
extension:

Debug.Print Workbooks("Book1.xls").Name

will work properly regardless of the "Hide Extensions" property
setting. The "Hide Extensions" setting also causes problems with the
FindWindowEx API call and with Excel Window captions.

The code to get around this stupid design decision is rather
complicated, and available as downloadable module file at
http://www.cpearson.com/Excel/FileExtensions.aspx . This same page
discusses the entire "Hide Extension" crap in detail.

FYI, you can access the "Hide Extension" setting from any Folder
window. Go to the Tools menu, choose Folder Options, then the View
tab, and then look for the "Hide Extension Of Known File Types".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)