Applescript: Automate Excel to convert .xls to .csv
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".
Solution 1:
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
Solution 2:
It appears that the Parameter error is due to changes with Office 2016 for Mac.
http://preview.alturl.com/f8srb
From what I've learned from that thread Excel V15 can only write automatically (triggered by AppleScript) within the path ~/Library/Containers/com.microsoft.excel/. It looks like I will have to either revert to the previous version of Excel, or maybe write the files to that folder and then copy to the desired location. What a PITA.