Save Excel CSV file as Excel XLSX file

Scripting Forums AppleScript | Mac OS X
#1

I had a routine that used to save a CSV file as XLSX now I get a parameter error Excel version V16.88.
This is the code that used to work any suggestions please.

tell application "Finder"
	set thefolder to alias "Macintosh HD:Users:petermitchell:Library:CloudStorage:Dropbox:DownloadsSafari:"
	open last item of (sort (get files of thefolder) by creation date)
	--The file download from the Web is downloaded in CSV format and offers an option to convert
	tell application "System Events"
		tell process "Microsoft Excel"
			delay 2 -- wait for the button to appear
			try
				click button "Convert" of window 1 -- click the "Don't Convert" button
			end try
		end tell
	end tell
	######Rename and Save as XLSX file
	set thefolder to "Macintosh HD:Users:petermitchell:Library:CloudStorage:Dropbox:DividendCapture2024:MasterFile:"
	-- Get the current date and time as a string
	set dateString to time string of (current date)
	-- Replace colons with hyphens
	set AppleScript's text item delimiters to ":"
	set dateItems to text items of dateString
	set AppleScript's text item delimiters to "-"
	set newDateString to dateItems as string
	-- Remove "AM" or "PM"
	set TrimedTime to text 1 thru -4 of newDateString -- Remove the last 3 characters (" AM" or " PM")
	set FileName to "RawData " & TrimedTime & ".xlsx"
	set thefolder to "Macintosh HD:Users:petermitchell:Library:CloudStorage:Dropbox:DividendCapture2024:MasterFile:Processed:"
	set destinationPath to thefolder & FileName
	tell application "Microsoft Excel"
		save workbook as active workbook filename destinationPath file format Excel XML file format
	end tell
	######
end tell
#2

Brave man - scripting Excel. I don’t envy you that one.

Anyway, there were a couple of problems with your script that I had to work through (ironically, I got this working first by using Numbers to create the Excel file, then back-ported it to Excel’s b0rked Apple-cum-VB-Script attempt).

First off, I closed the Finder tell block after getting the file - there’s no further use of the Finder here, and it was complicating the opening of the document (OMM it always wanted to open .csv files in Numbers - closing the Finder block fixed that).

Secondly, I don’t know which version of Excel you’re using, but I don’t get the ‘Convert’ popup, so I stripped that out. You may need it if you’re running a different version of Excel than I am

Third, the line:

	set destinationPath to thefolder & FileName

needed some love, because this creates a list {“Macintosh HD:User:yada yada yada”, “RawData x:y:z.xlsx”.
This is semi-easily done by changing the line to:

	set destinationPath to thefolder & FileName as text

but in order to do that I needed to reset the text item delimiters to avoid an extraneous ‘-’ (this is always best practice anyway).

Finally, we get to the crux of the issue which is Excel’s abomination of a save command.

Despite what the dictionary tells you, you can (and should) ignore the file format parameter. This is what was choking your script.
When there’s no explicit file format (and I don’t care to dig into why it doesn’t work), Excel uses the specified file name to infer the file type. Since you include ‘.xlsx’ in the file name, it automatically assumes an XML workbook.

Here’s the amended script. You might need to backfill some of the work around the Convert button if that’s still an issue.

tell application "Finder"
	set thefolder to alias "Macintosh HD:Users:petermitchell:Library:CloudStorage:Dropbox:DownloadsSafari:"
	--The file download from the Web is downloaded in CSV format and offers an option to convert
	set theFile to last item of (sort (get files of thefolder) by creation date)
end tell

tell application "Microsoft Excel"
	open theFile
end tell

######Rename and Save as XLSX file
-- this line doesn't seem to serve any purpose, so I commented it out:
--set thefolder to "Macintosh HD:Users:petermitchell:Library:CloudStorage:Dropbox:DividendCapture2024:MasterFile:"

-- Get the current date and time as a string
set dateString to time string of (current date)
-- Replace colons with hyphens
set oldDelims to my text item delimiters -- save the current TIDs for later
set my text item delimiters to ":"
set dateItems to text items of dateString
set my text item delimiters to "-"
set newDateString to dateItems as string
set my text item delimiters to oldDelims -- always restore TIDs after you're done with them

-- Remove "AM" or "PM"
set TrimedTime to text 1 thru -4 of newDateString -- Remove the last 3 characters (" AM" or " PM")
set theFileName to "RawData " & TrimedTime & ".xlsx"
set thefolder to "Macintosh HD:Users:petermitchell:Library:CloudStorage:Dropbox:DividendCapture2024:MasterFile:Processed:"
set destinationPath to thefolder & theFileName --as text

tell application "Microsoft Excel"
	save workbook as active workbook filename destinationPath 
end tell
######
#3

You can also keep the string manipulation in Excel and not have to mess with text item delimiters at all:

tell application "Microsoft Excel"
	open theFile
	
	set newDateString to evaluate name "=TEXT(NOW(),\"mm-dd-yyyy hh-mm-ss am/pm\")"
	set trimmedTime to text 1 thru -4 of newDateString
end tell

Or use whatever date/time format you want in Excel’s TEXT function.

#4

1st Thank you very much I cannot tell you how long I tried to fix that issue myself. Maybe irrelevant but the “convert” is necessary as the file downloaded from the web opens with a dialog box with an option of closing it which I have to do to proceed. Into the dictionary.
Also RoosterBoy have to put that in my dictionary saved a lot of lines, had to change starting position as only wanted the time.

Thanks both of you

#5

If you just want the time, you can do this:

tell application "Microsoft Excel"
	set timeString to evaluate name "=TEXT(NOW(),\"hh-mm-ss am/pm\")"
	set trimmedTime to text 1 thru -4 of timeString
end tell

This results in trimmedTime being set to something like "07-16-13".

If you want it in 24-hour time, do this instead:

tell application "Microsoft Excel"
	set timeString to evaluate name "=TEXT(NOW(),\"hh-mm-ss\")"
end tell

Result: 19-16-13

#6

Some alternative date/time manipulation can be used instead of text item delimiters:

set iso8601DT to ((current date) as «class isot» as string)
-- 2024-09-14T08:05:35

-- because colons in a time string can be misconstrued as a folder path in AppleScript
set iso8601date to (do shell script "sed 's/:/•/g' <<<" & iso8601DT) as text
-- 2024-09-14T08•05•35

set iso8601 to text 1 thru ((offset of "T" in iso8601date) - 1) of iso8601date
-- 2024-09-14