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:
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
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:
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:
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”.