I created this macro in response to a user's thread at Batch convert CVS to XLSX file (see my post there).
I decided to post my macro here too because I thought it may be of any worth to those of you who might need to convert spreadsheets in batch.
The macro is based on an AppleScript script. The script prompts you to select Numbers-compatible files and allows to choose from several legible formats. It puts converted files in the "Documents" folder. I created it targeting Numbers 3.2.2 in OS X Mavericks, in KM 6.4.8, and modified to work with Mojave 10.14.6. It includes small chunks of GUI scripting which is prone to errors when the GUI structure changes between releases of macOS (especially now that Big Sur is available), however I don't expect the part I added to evolve significantly by the current generation of the OS to the point of crashing the macro. If the crashes do occur, return and update me.
Updated 2020-11-18T20:20:00Z
I re-wrote the AppleScript to allow for different versions of macOS with regard to the GUI segment correction for macOS Mojave 10.14.6. I don't run newer iterations of macOS and can't adjust the macro appropriately, so if you run into unintended behaviour your should figure out on your own: it shouldn't pose additional problems since you have the framework along which you work it out.
The macro is now triggered with the hot keys ⌃⌥⇧⌘P by default. You can assign different ones.
IMPORTANT: make sure the group containing this macro is enabled and active otherwise it won't run outside the Macro Editor.
Convert selected spreadsheets into another format.kmmacros (5.3 KB)
The AppleScript script is as follows:
set NumbersCompatibleDocs to choose file of type {"com.apple.iwork.numbers.numbers", "public.spreadsheet", "public.comma-separated-values-text", "org.openxmlformats.spreadsheetml.sheet"} default location (path to documents folder) with prompt "Select Numbers spreadsheets or spreadsheets of compatible formats:" with multiple selections allowed
property ExportPath : POSIX path of (path to documents folder)
property ExportFormats : {"PDF", "Microsoft Excel", "CSV", "Numbers 09"}
property Ext : {".pdf", ".xlsx", ".xls", ".numbers", ".csv"}
property SystemVersion : (system info)'s system version
try
tell application "Numbers"
activate
tell application "System Events" to set frontmost of application process "Numbers" to true
set CurrentDocuments to open NumbersCompatibleDocs
set TargetFormat to choose from list ExportFormats with title "Export formats" with prompt "Choose a format:" default items {"CSV"} without multiple selections allowed and empty selection allowed
if result is not false then
set TargetFormat to TargetFormat's item 1
repeat with aDoc in CurrentDocuments
set docName to the name of aDoc
set BaseName to my GetBaseName(docName)
-- log "BaseName ⬆︎" & (log BaseName)
if TargetFormat is "CSV" then
set DocumentPath to ExportPath & BaseName & ".csv"
set ExportFile to (POSIX file DocumentPath) of me
-- log "DocumentPath ⬆︎" & (log DocumentPath)
export aDoc to ExportFile as CSV
else if TargetFormat is "PDF" then
set DocumentPath to ExportPath & BaseName & ".pdf"
set ExportFile to (POSIX file DocumentPath) of me
export aDoc to ExportFile as PDF
else if TargetFormat is "Microsoft Excel" then
set DocumentPath to ExportPath & BaseName & ".xlsx"
set ExportFile to (POSIX file DocumentPath) of me
export aDoc to ExportFile as Microsoft Excel
else
set DocumentPath to ExportPath & BaseName & ".numbers"
set ExportFile to (POSIX file DocumentPath) of me
export aDoc to ExportFile as Numbers 09
end if
end repeat
else
return
end if
end tell
display notification "Export successful"
on error errMsg number errNumber
tell application "Numbers"
activate
display alert "Export failed with error " & errNumber & return & return & errMsg as warning buttons {"OK"} giving up after 3
end tell
return
end try
considering numeric strings
if SystemVersion > "10.9.5" then
set AccessibilityHelpDescription to "Change the item grouping (hold down Option to change the sort)"
else
set AccessibilityHelpDescription to "Change the item arrangement"
end if
end considering
tell application "Finder"
tell folder "Documents" of home
open
activate
sort every document file by creation date
end tell
set current view of Finder window "Documents" to column view
delay 1
tell application "System Events"
tell application process "Finder"
set frontmost to true
tell window 1
tell toolbar 1
tell (the first group whose help is AccessibilityHelpDescription)
tell menu button 1
perform action "AXPress"
repeat until menu 1 exists
delay 0.1
end repeat
tell menu 1
tell menu item "Date Created" to perform action "AXPress"
end tell
end tell
end tell
end tell
end tell
end tell
end tell
end tell
on GetBaseName(FullName)
set OriginalName to FullName
set AppleScript's text item delimiters to Ext
get text item 1 of OriginalName
return result
end GetBaseName