Excel Scripting Question
Posted April 23rd, 2006 @ 08:04am by Erik J. Barzeski
My country club posts their tee times as three .txt files that are included between <pre> tags. The process has been: 1) edit saturday.txt (or sunday, or holiday), 2) upload via FTP.
This year they'd like to do the tee times in Excel. Clearly they can "save as" a .txt file, but I was wondering if there was a way to streamline the process: perhaps automating both saving the .xls file (which could have three sheets for Saturday, Sunday, and Holiday tee times) and the three .txt files. If the files could somehow automatically be uploaded via FTP, too, that'd be great.
Does anyone have any ideas on how this could be done?
Donate Life
Posted 24 Apr 2006 at 10:09am #
If they're using a new version of MS Office, Office files are now (or can easily be) XML files. If that's the case, XSLT could be useful here.
Posted 24 Apr 2006 at 11:29am #
It might be easier to have them continue to use the TXT file and if they want to put it into an Excel file, convert *from* the TXT to Excel (instead of the other way around). Nowadays, the copy on the web tends to be *the* master copy; when not, it tends to be neglected and outdated.
I suppose (worst case) you could have them simply do File -> Save as HTML (I believe Excel has some version of this functionality in recent versions) and then upload that via FTP.
Posted 24 Apr 2006 at 11:31am #
Dave, you're missing the point. They want to use Excel because a lot of the same people keep the same groups, so moving columns and rows around in Excel is much easier than doing so in text. And no, save as HTML is NOT an option in the least for more than obvious reasons.
Posted 24 Apr 2006 at 5:57pm #
It is ugly and very painful, but Excel fully supports VBA (Visual Basic for Applications). I'm not sure if Office for Mac supports VBA the same as Windows. YMMV.
Without to much effort, one could craft a little VB code to export each of the sheets as a text file. In the Windows environment, you could probably even reference a component (DLL) to handle the FTP portion as well.
A brief sample of code is available here: http://www.tek-tips.com/viewthread.cfm?qid=1210651&page=8
Note that the sample page only exports the current active sheet. You would have to include additional logic to handle multiple sheets as you mentioned...