I'm trying to update a script I wrote that takes an Excel spreadsheet,
saves it as tab-delimited text, and uploads it to an FTP site. Problem
is, I cannot see the file format listed in the Excel 2004 dictionary
when I open it with Script Editor (there are so many file formats, it
truncates after Excel3).
Can you tell me the correct way to specify the Text/Tab-delimited file
format? I assume it's something like:
tell application "Microsoft Excel"
open "path:to:my:Excel:document"
-- wish I knew the correct text to specify tab-delimited
... but I've been unable to guess the correct format name.
There are actually two ways. Neither of them uses the simple 'open' from the
Standard Suite (although chances are that too will work anyway for a regular
tab-delimited text file). You need either 'open workbook' or 'open text
file' from the Microsoft Excel Suite. These list the format parameters
clearly as part of their own syntax in Script Editor. ('open' does not list
any 'format' parameter so it does you no good to look up the 'file format'
class.) 'open workbook' returns a result, so you can set a variable to the
opened document if you wish.
set myWorkbook to open workbook workbook file name
"path:to:my:Excel:document" format tab delimiter
will be more than good enough for simple files where you can use "General"
format for every column (numbers as numbers, dates as dates, everything else
as text). If you omit the optional [format] parameter it will use whatever
is current. The format parameter has 5 enumerations : tab delimiter/commas
delimiter/spaces delimiter/semicolon delimiter/no delimiter/custom character
delimiter . If you use 'custom' then here's a separate 'delimiter' parameter
where you specify what the delimiter is, e.g. delimiter ";".
Then there's 'open text file' where you can specify multiple delimiters and
also specify which type of number/date/text format you want for each
individual column, just as you do in the Open wizard in the UI, using the
'field info' parameter. But that's rather complicated - ask me if you need
that one. To open a delimited text file -- which dies not need to be an
Excel file-- that can use "General" for every column, you can omit field
info:
open text file filename "path:to:my:text:file" with tab
Whenever you see a parameter like tab that says it's 'boolean', meaning true
or false, you can type 'true' or 'false' but the AppleScript compiler
changes that to 'with' or 'without' respectively, so I end up just typing it
that way myself.
save as sheet "my sheet" filename "my file.txt" file format text tab
delimited
That method is not quite correct for saving a single sheet as a tab text
file. You did find the 'save as' command in the Excel Suite, which is better
than the simple 'save' in Standard Suite - although that too has an 'as'
parameter [save sheet "my sheet" as text Mac]. But you can only use the
enumerations offered by the 'save as' command: you can't throw in words like
'delimited' which don't appear there. (Excel evidently just ignored that and
any other terms you'd throw in at the end of the line.) The correct format
enumeration is ' text Mac file format'. So:
save as sheet "my sheet" filename "my file.txt" file format text Mac
file format
close active window without saving
I'm glad you found that! (The correct term is 'saving no' but 'without
saving' works.) If you use 'saving yes' as you should be able to when there
are no unsaved changes you get the save as dialog on screen.
--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <
http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <
http://macscripter.net/scriptbuilders/>
Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.
PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.
