Automating numbers, Exporting the file as an excel document

applescriptautomatornumbers

I have recently acquired a Macbook Pro (OS X Yosemite 10.10.4). I find the Numbers application satisfactory to work with, but I save the file as an .xlsx file after I am done. I need to make changes to the file repeatedly and save it repeatedly as the file is used as input by a program that can only read excel files.

As a consequence, every time I make a change in the file I have to go to the Menu bar, pick export as Excel, choose to replace the pre-existing excel file. I have gone through some tutorials of applescript used for 'Numbers', but I cannot find out how to automate the process of exporting a file as Excel.

on run {input, parameters}

    tell application "Numbers"
        activate
        tell document 1
            export as xlsx
        end tell
    end tell
    return input
end run

This is the code as it stands. Please let me know how to automate the process of exporting a numbers file as an excel file.

Best Answer

I've written a script to export to CSV originally. I've included a commented out line in that script you can switch to, to export to Excel instead:

See the gist here: https://gist.github.com/idStar/61994506d69595da3d30

Additionally, the entire script is reproduced below:

#! /usr/bin/osascript

(*
---------------------------------------------------------------------------------

 Script: SpreadsheetExportToCSV

 Command-line tool to convert a spreadsheet document to CSV
 This AppleScript is tested with and compatible with Apple iWork Numbers 3.6,
 current as at October 23, 2015.

 Parameters:
 1. Full Path to the input file, including file extension
 2. Full Path to the output file, including file extension

 Example command-line invocation:

    osascript SpreadsheetExportToCSV.scpt "/Users/me/Documents/MySpreadsheet.xlsx" "/Users/me/Documents/Converted/OutputFile.csv"

 The spreadsheet to use as an input file can be an Excel file or a Numbers file.

 Sohail Ahmed
 Blog: http://sohail.io
 Twitter: @idStar

 Creation Date: October 23, 2015

---------------------------------------------------------------------------------
*)

global _inputFilePathAlias
global _outputFilePath
global _requestedOptions


(*
 run

 This is our entry point, our main function, where this script 
 begins execution. We call out to helper functions, to modularize
 the design.
*)
on run argv
    -- Ensure our CSV files are encoding with UTF8:
    ensureUTF8Encoding()

    -- Parse and determine input/output paths:
    retrieveCommandLineArguments(argv)

    -- Perform the actual activation, file open, export and cleanup:
    processSpreadsheet()

end run




---------------------- SUPPORTING FUNCTIONS --------------------------

(*
 retrieveCommandLineArguments

 Handles parsing the command line arguments passed to us.
 We return a list, where the first element is the input file
 path as an alias. The second element is the output path,
 as text (as it may not yet exist).
*)
on retrieveCommandLineArguments(command_line_arguments)
    set _inputFilePathAlias to POSIX file (item 1 of command_line_arguments) as alias
    set _outputFilePath to (POSIX file (item 2 of command_line_arguments)) as text

    log "input file path is: " & _inputFilePathAlias
    log "output file path is: " & _outputFilePath

end retrieveCommandLineArguments


(*
 processSpreadsheet

 This function is the workhorse of this script. We open Numbers,
 have it load the source spreadsheet, and invoke the export command
 to ultimately write the output CSV to the specified path.
*)
on processSpreadsheet()
    tell application "Numbers"
        activate

        -- Before we open the file asked of us, close out every document 
        -- that might have opened along with the application having activated:
        close every window saving no

        -- Retrieve information about the source file:
        set fileInfo to (info for (_inputFilePathAlias))
        set fileName to name of (fileInfo)
        set fileExtension to name extension of (fileInfo)
        log "Opening source document " & fileName & "..."

        tell (open _inputFilePathAlias)
            -- In this scope, we are now implicitly dealing with the document just opened
            -- as the current target, which means we access it through the "it" keyword,
            -- as per: https://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/AppleScriptLangGuide/conceptual/ASLR_fundamentals.html#//apple_ref/doc/uid/TP40000983-CH218-SW4

            set activeDocument to it

            -- Note: We could have also gotten to the active document by walking the chain from the top,
            -- i.e. right from the Application object:
            --set activeDocument to document 1 of application "Numbers" 

            say "Starting Export."
            with timeout of 600 seconds
                export activeDocument as CSV to file _outputFilePath
                -- Use this instead if you want to export to Excel:
                -- export activeDocument as Microsoft Excel to file _outputFilePath 
            end timeout
            say "Completed Export."

            -- Since we closed out other windows that might have been open before
            -- opening the file we sought, we really should only have one document
            -- window open.
            close activeDocument
        end tell

        quit

    end tell
end processSpreadsheet


(*
 ensureUTF8Encoding

 Microsoft Excel on the Mac is not good with exporting special
 characters as is Apple Numbers. Part of this is in the ability for
 Numbers to correctly process UTF8 formatting when exporting.

 Setup default export encoding for CSV files to UTF8, so without 
 specifying anything further for AppleScript, the right format will 
 be applied automatically. Since we cannot specify the CSV export
 encoding via AppleScript, we will set it via the Defaults Database
 with a shell command.

 Here are the codes that apply: 4=UTF8, 12=windows latin, 30=MacRoman
 As such, we'll specify 4 for UTF8.

 This technique courtesy of: https://discussions.apple.com/thread/4018778?tstart=0 
*)
on ensureUTF8Encoding()
    do shell script "/usr/bin/defaults write com.apple.iWork.Numbers CSVExportEncoding -int 4"
end ensureUTF8Encoding