Can anyone help me figure out why this Applescript no longer works in Excel 2016? The script works fine without any issues in Excel 2011. The script opens an Excel file, deletes a column and removes any “,” or “;” from the Excel file and then saves it as a CSV file. The issue I’m running into in Excel 2016 is the last piece to save it as a CSV file after the manipulation. Nothing is saved and I don’t get any errors.
set theWorkbookFile to choose file with prompt "Please select the XLS file" of type {"xls", "xlt"} #Only display file type of .xls
tell application "Microsoft Excel"
activate
open theWorkbookFile #open the xls file
set theWorksheetname to name of worksheet 1 of active workbook
set theWorksheet to worksheet 1 of active workbook
activate object theWorksheet
tell application "System Events" to set visible of process "Microsoft Excel" to false
#Remove the first column
tell theWorksheet
delete range column 1 shift shift to left
try
##remove any "," and ";" from the product description and replace it with a " " instead.
replace (range "B:B" of worksheet "Sheet1") what "," replacement " "
replace (range "B:B" of worksheet "Sheet1") what ";" replacement " "
end try
end tell
#Set the temp csv file to the name of of the Excel sheet followed by -TMP.csv
set theFile to theWorksheetname & "-TMP" & ".csv" as text
save as theWorksheet filename theFile file format CSV file format with overwrite #save the file in csv format and overwrite if it exist
close active workbook saving no #close the csv file without prompting user to save again
end tell