Ha ha! I finally got some time to figure out how to get the ET.XLS spreadsheet to support DMY format instead of the default MDY (US) format. It turned out to be a small change to the macros, but unfortunately required hard-coding the number of columns in the input files to make it work. As long as you aren't changing the number of columns in the TXT files, this should work for you. I also had to remove the forced "m/d" format on one of the worksheet tabs.
You can download a copy of the zipped spreadsheet here: et2_DMY_dates.zip. I also updated my www.staqs/sbtm page to include this file.
Last July I posted the latest tools-ruby scripts and received some feedback that I wasn't the only one with this problem. I fixed the date formatting problem using Excel 2003, so if anyone is using an older version of Excel too bad. ;)
As for Excel 2007, I just started using it this week. I noticed that I had to play with the Macro security settings (once you make the 'Developer' toolbar visible), and then I got it to work. I'll be playing with this a bit more in the coming days so if an update is required I'll post it here to let you know.
If you find this updated file helpful, please drop me a line to let me know. Thanks.
Cheers!
Okay, here's the quick fix for Excel 2007.
ReplyDeleteI spent some time working with it today but it looks like ideally I would have to rebuild the spreadsheet from scratch and I don't have the time for that right now.
If you are working with Excel 2007 here's how to convert the ET.XLS file to 2007 format:
1. Copy the ET.XLS (MDY) or ET2.XLS (DMY) file to your C:\Sessions folder (or wherever your base location is)
2. Open MS Excel (with a blank workbook, not by clicking one of these files)
3. Click the [Office button] > [Excel Options] > [Trust Center] > [Trust Center Settings...]
4. In the "Trust Center" window, click [Trusted Locations] and then [Add new location...] down in the lower right corner
5. Browse to or enter the path - e.g. "C:\Sessions" - AND *select* the "Subfolders of this location are also trusted" check box.
Click [OK] and [OK] again to save the changes.
=> Macros for the files in the C:\Sessions folders should now work without complaint in Excel.
..Now for the spreadsheet...
6. Open ET.XLS or ET2.XLS in Excel 2007. If you see the Macro security warning at the top, then "Enable" any warnings you receive.
7. [Office button] > Save As > "Excel Macro-Enabled Workbook"
(this will create an *.XLSM file)
(here's the fun part)
8. Exit Excel and open the XLSM file you just saved.
=> If you are like me, you may see a warning that says something like: "Excel found unreadable content... blahblahblah"
- Click [Yes] to fix the file.
- click [Close] to the dialog that appears telling you what it fixed.
9. SAVE the file again to a *different* filename.
(I don't know why it needs to be different, but it didn't work for me when I tried to save it with the same file name.)
10. Close the file and re-open the new XLSM file.
=> All the Macros should now work without complaint! Voilà!
(Remember to delete the intermediate file you created in step 7 above.)
Again, I'd prefer to rebuild the file from scratch, but these steps above should work for you in the meanwhile if you want to do the upgrade yourself.
Please let me know if you have any questions or comments to share.
Happy testing! Cheers!