I recently had the need to build data for a client in an Excel spreadsheet. Each tab in the spreadsheet had information that was easily editable by business staff. But the data in each tab had to eventually be processed by a Ruby script for manipulation and eventual insertion into a database.

I’m working on a Mac and wanted to learn Applescript in more depth so I decided to automate the creation of the CSV files - one for each tab. The examples I found were sparse or incorrect so I’m providing the solution here. In order to use this you’ll need to copy the code below into the Script Editor and save the script. I’m using Mac Office 2008 so I don’t know if this works with previous versions or not.

set theFile to (choose file with prompt "Choose Excel file") as string
set value to ""
tell application "Microsoft Excel"
    open theFile
    set fileFormat to (class of (file format of active workbook))
    display dialog fileFormat
    set allSheets to (every worksheet in active workbook)
    repeat with i from 1 to (count allSheets)
        set theWorksheet to (item i of allSheets)
        activate object theWorksheet
        set sheetName to (name of theWorksheet as text)
        set csvName to sheetName & ".csv"
        save as active sheet filename csvName file format CSV file format
    end repeat
    close active workbook without saving
end tell