Re: What happened to the Applescript dictionary in Excel?

From: Paul Berkowitz (berkowit_at_spoof_silcom.com)
Date: 11/21/04

  • Next message: Paul Berkowitz: "Re: Excel X and Applescript" 
    Date: Sun, 21 Nov 2004 08:31:57 -0800

    On 11/20/04 6:19 AM, in article
    6cf097cd.0411200619.1082c6ea@posting.google.com, "Brad Koehn"
    <brad@koehn.com> wrote:

    > I'm trying to update a script I wrote that takes an Excel spread***,
    > 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 *** "my ***" filename "my file.txt" file format text tab
    > delimited

    That method is not quite correct for saving a single *** 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 *** "my ***" 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 *** "my ***" 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.

    > end tell 

    -- 
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.
  • Next message: Paul Berkowitz: "Re: Excel X and Applescript"
    • Quantcast