AppleScript to Export Excel 2011 XLSX as Unicode format text?

I’m trying to update a short AppleScript script that I use to export the contents of a sheet from an Excel 2011 spreadsheet in text format. Currently the script exports the content as standard text. However, I now need it to preserve accented characters as found in European person and place names.

Here’s my current script:

set theFile to "Macintosh HD:Users:peter:Documents:excel_src.xls" as text

set savedCourseData to "Macintosh HD:Users:peter:Documents:excel_tempdatafile.txt" as text

tell application "Macintosh HD:Applications:Microsoft Office 2011:Microsoft Excel.app"
	activate
	open theFile
	select sheet "Course-Info"
	save sheet in savedCourseData as text Mac file format without CreateBackup
	close workbook "excel_tempdatafile.txt" saving no
end tell

The problem I’m having is finding the correct way to change the line:

“save sheet in savedCourseData as text Mac file format without CreateBackup”

I’ve tried replacing “as text Mac file format” with the following, but all produce error messages:

“as «class ut16»”
“as «class ut16» text”
“as «class utf8»”
“as Unicode”
“as Unicode text”

etc!

Any suggestions please, I really need to get this working ASAP?!

Hi,

first of all create backup is a enumerated constant containing a space character, but false is the default value so you can omit it.
I tested the script with some diacritics and all characters are exported correctly.
It’s not needed either to select explicitly the sheet, you can save the sheet by name


set documentsFolder to path to documents folder as text
set theFile to documentsFolder & "excel_src.xls"
set savedCourseData to documentsFolder & "excel_tempdatafile.txt"

tell application "Microsoft Excel"
	activate
	open theFile
	save sheet "Course-Info" in savedCourseData as text Mac file format
	close workbook "excel_tempdatafile.txt" saving no
end tell

Hi Stefan

Thanks for the response. However it doesn’t preserve all accented characters. My test spreadsheet contains the following lines:

Nicolas Lipscömbe
David Jackšon
Adam Daviés
Es èéêëēėę
à bçdèfghîjkłmñôpqrßtûvwxÿž
ÀBÇDÈFGHÎJKŁMÑÔPQRŚTÛVWXŸŽ

The script produces the following output:

Nicolas Lipscömbe
David Jack_on
Adam Daviés
Es èéêë___
à bçdèfghîjk_mñôpqrßtûvwxÿ_
ÀBÇDÈFGHÎJK_MÑÔPQR_TÛVWXŸ_

So whilst many of the diacriticals have been preserved OK, some of them have been substituted with the ‘_’ (underscore) character! If I manually save from Excel, selecting the “UTF-16 Unicode Text (.txt)” format in the process, I get the following text output with all the diacriticals preserved:

Nicolas Lipscömbe
David Jackšon
Adam Daviés
Es èéêëēėę
à bçdèfghîjkłmñôpqrßtûvwxÿž
ÀBÇDÈFGHÎJKŁMÑÔPQRŚTÛVWXŸŽ

So Excel can preserve all the accented characters but only if told to use UTF-16; its default behaviour preserves some of them only!!

Success, but weird!

After a considerable amount of searching I came across an item from 2004 about exporting Unicode from Excel and how Microsoft had forgotten to define the constant required in the AppleScript dictionary for Excel:

http://www.office-archive.com/2-excel/1bb3484bffbd4156.htm

From this web link it seems that the “magic” value needed is “42”, which is used as follows:

Amazingly this works! So, here is my short script updated to reflect this discovery:


set documentsFolder to path to documents folder as text
set theFile to documentsFolder & "excel_test:excel_src.xls" as text
set savedCourseData to documentsFolder & "excel_test:excel_tempdatafile.txt" as text

set UniCodeFormat to 42

tell application "Macintosh HD:Applications:Microsoft Office 2011:Microsoft Excel.app"
	activate
	open theFile
	save sheet "Course-Info" in savedCourseData as UniCodeFormat
	close workbook "excel_tempdatafile.txt" saving no
end tell

Finally, I’d like to pay my respects to the late Douglas Adams for predicting that “42” would be the solution I’d need when he wrote that the “Answer to the Ultimate Question of Life, The Universe, and Everything” was “42” in his brilliant book “The Hitchhiker’s Guide to the Galaxy”.