Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Workbook.Name result
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Workbook.Name result
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) On Wed, 5 Aug 2009 06:34:09 -0700 (PDT), Ray wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Autofilter Source Workbook A result in Destination Workbook BSheet1 | Excel Programming | |||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook | Excel Programming | |||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook | Excel Worksheet Functions | |||
Look up 2 values, return result in a different workbook | Excel Worksheet Functions | |||
Sum data in workbook 1 and write result in workbook 2 | Excel Programming |