MacOS – Applescript: Automate Excel to convert .xls to .csv

applescriptcsvmacosms office

I'm trying to batch process converting a folder of .xls files to .csv using Applescript for MS Excel V15.15. I'm using a sample I found online to model it :

set theOutputPath to (path to desktop folder as string) & "My Saved Workbook.csv"
  tell application "Microsoft Excel"
    tell active workbook
      save workbook as filename theOutputPath file format CSV file format
    end tell
  end tell

This is the script that fails consistently, though it seems to most closely resembles the model:

set csv_folder to "Macintosh HD:Users:Me:CSV:" & file_name as string
    tell application "Microsoft Excel"
        open Source_file
        tell active workbook
            save workbook as filename csv_folder file format CSV Mac file format-->
           (*This generates error "Microsoft Excel got an error: Parameter error." number -50 *)
        end tell
    end tell

I have also tried:

set csv_folder to "Macintosh HD:Users:Me:CSV:" & file_name & ".csv" as string

tell application "Microsoft Excel"
    open Source_file
        tell active workbook
            save workbook as filename csv_folder -->
           (*This usually generates error "Microsoft Excel got an error:
            Parameter error." number -50 the first time it is run, 
              then works the 2nd time *)
        end tell
end tell

EDIT:This latter script, though it completes does not result in a true csv file, as when I open it with BBEdit it is showing code, not the file contents.

I have also tried setting the cv_folder without using "as String". Any ideas why this fails? It does not seem to like the syntax "save workbook as filename theOutputPath file format CSV file format".

Best Answer

For Excel V15, use a posix path, like this:

set theOutputPath to POSIX path of ((path to desktop folder as string) & "My Saved Workbook.csv")
tell application "Microsoft Excel"
    tell active workbook
        save workbook as filename theOutputPath file format CSV Mac file format with overwrite
    end tell
end tell

Edit 1 -

I test it on V15.15 and V15.16, it's a bug when the destination folder doesn't contains a recently opened Excel file. So use it

set theOutputPath to POSIX path of ((path to desktop folder as string) & "My Saved Workbook.csv")
set parentFolder to (do shell script "dirname " & quoted form of theOutputPath) as POSIX file -- get the parent folder 

tell application "Microsoft Excel"
    alias parentFolder -- a folder where to save a new file, workaround to a bug when the destination folder doesn't contains a recently opened Excel file
    save workbook as active workbook filename (theOutputPath) file format CSV Mac file format with overwrite
end tell

Edit 2 -

Or create an empty file like this

set theOutputPath to POSIX path of (path to desktop folder as string) & "My Saved Workbook.csv"
do shell script "touch " & quoted form of theOutputPath -- create an empty file
set theOutputPath to theOutputPath as POSIX file
tell application "Microsoft Excel"
    save workbook as active workbook filename (theOutputPath) file format CSV Mac file format with overwrite
end tell

Edit 3 : I had another idea, it would be the simplest script.

set theOutputPath to (path to desktop folder as string) & "My Saved Workbook.csv"
tell application "Microsoft Excel"
    alias theOutputPath --  workaround to a bug when the destination folder doesn't contains a recently opened Excel file
    save workbook as active workbook filename theOutputPath file format CSV Mac file format with overwrite
end tell
Related Question