I have a script I use to convert .doc to docx. however if I try to adapt it to Excel, it does not compile.
on open of theFiles
repeat with x in theFiles
tell application "Microsoft Word"
open x
save as the front document file name path of the front document & ". xlsx" file format format document
close front document saving no
end tell
end repeat
end open
I did, and thanks. But now the script hangs on the file format. And I can not find, in the dictionary, what file format to use for Excel 2011.
here is what I have:
on open of theFiles
repeat with x in theFiles
tell application "Microsoft Excel"
open x
save as the front document filename path of the front document & ". xlsx" file format format ?????
close front document saving no
end tell
end repeat
end open
Looking carefully in the Excel AppleScript dictionary, I wrote :
script 1
tell application "Microsoft Excel"
properties of document 1
-->Excel98to2004 file format, if the file is an xls one
-->Excel XML file format, if the file is an xlsx one
end tell
After that, I knew the format descriptor to apply
and was able to write :
script 2
tell application "Microsoft Excel"
set thePath to full name of document 1
tell application "SystemUIServer" # used because Excel refuse to compile offset
(reverse of characters of thePath) as text
offset of "." in result
end tell
set destPath to text 1 thru -result of thePath & ".xlsx"
save workbook 1 in destPath as Excel XML file format
end tell
Yvan KOENIG (VALLAURIS, France) dimanche 23 septembre 2012 17:13:48
In AppleScript Editor
¢ press ⇧⌘O (Open Dictionary)
¢ choose Microsoft Excel
¢ type save as in the search field
¢ click save as
You will see
save as‚v : Saves changes into a different file.
save as sheet
filename text : A string that indicates the name of the file to be saved. You can include a full path. If you don't, Microsoft Excel saves the file in the current folder.
[file format CSV file format/ŒCSV Mac file format/ŒCSV MSDos file format/ŒCSV Windows file format/ŒDBF3 file format/ŒDBF4 file format/ŒDIF file format/ŒExcel2 file format/ŒExcel 2 east asian file format/ŒExcel3 file format/ŒExcel4 file format/ŒExcel5 file format/ŒExcel7 file format/ŒExcel 4 workbook file format/Œinternational add in file format/Œinternational macro file format/Œworkbook normal file format/ŒSYLK file format/Œcurrent platform text file format/Œtext Mac file format/Œtext MSDos file format/Œtext printer file format/Œtext windows file format/ŒHTML file format/ŒXML spreadsheet file format/ŒPDF file format/ŒExcel binary file format/ŒExcel XML file format/Œmacro enabled XML file format/Œmacro enabled template file format/Œtemplate file format/Œadd in file format/ŒExcel98to2004 file format/ŒExcel98to2004 template file format/ŒExcel98to2004 add in file format] : Specifies the file format to use when you save the file.
[password text] : A case-sensitive string, no more than 15 characters, that indicates the protection password to be given to the file.
[write reservation password text] : A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.
[read only recommended boolean] : Set to true to display a message when the file is opened, recommending that the file be opened as read-only.
[create backup boolean] : Set to true to create a backup file.
[add to most recently used list boolean] : Set to true to add this workbook to the list of recently used files. The default value is false.
[overwrite boolean] : Set to true to automatically overwrite an existing file.
[save as local language boolean] : True saves files against the language of Microsoft Excel. False is the default, which saves files against the language of Visual Basic for Applications
or the save workbook as command
save workbook as‚v : Saves changes into a different file.
save workbook as workbook
[filename text] : A string that indicates the name of the file to be saved. You can include a full path. If you don't, Microsoft Excel saves the file in the current folder.
[file format CSV file format/ŒCSV Mac file format/ŒCSV MSDos file format/ŒCSV Windows file format/ŒDBF3 file format/ŒDBF4 file format/ŒDIF file format/ŒExcel2 file format/ŒExcel 2 east asian file format/ŒExcel3 file format/ŒExcel4 file format/ŒExcel5 file format/ŒExcel7 file format/ŒExcel 4 workbook file format/Œinternational add in file format/Œinternational macro file format/Œworkbook normal file format/ŒSYLK file format/Œcurrent platform text file format/Œtext Mac file format/Œtext MSDos file format/Œtext printer file format/Œtext windows file format/ŒHTML file format/ŒXML spreadsheet file format/ŒPDF file format/ŒExcel binary file format/ŒExcel XML file format/Œmacro enabled XML file format/Œmacro enabled template file format/Œtemplate file format/Œadd in file format/ŒExcel98to2004 file format/ŒExcel98to2004 template file format/ŒExcel98to2004 add in file format] : Specifies the file format to use when you save the file.
[password text] : A case-sensitive string, no more than 15 characters, that indicates the protection password to be given to the file.
[write reservation password text] : A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.
[read only recommended boolean] : Set to true to display a message when the file is opened, recommending that the file be opened as read-only.
[create backup boolean] : Set to true to create a backup file.
[access mode exclusive/Œno change/Œshared] : Specifies the access mode for the new file.
[conflict resolution local session changes/Œother session changes/Œuser resolution] : Specifies who conflict resolutions will be handled.
[add to most recently used list boolean] : Set to true to add this workbook to the list of recently used files. The default value is false.
[overwrite boolean] : Set to true to automatically overwrite an existing file.
after file format there is a list of available formats. the values are enumerated constants, so you have to use them without quotes.
The parameters in brackets [] are optional
PS: If you want to have the converted file in the same dictionary, omit the filename parameter.
The file extension .xlsx will be added automatically
LIke StefanK makes a point of, you have to learn to read the dictionary like a lawyer reads the law.
Then there is the technique Yvan Koenig showed you a really great way to figure out properties of an entity in Applescript by getting the properties, this is of tremendous help when you want to see what is going on, or understand what properties and entity has.
This is hacking and exploration, and fun, and it gives the best answers really, though dictionaries, aren’t telling the full truth about how things really are at all times.
Applying those two practices, ensures that you are able to make your scripts workable in the shortest amount of time.
A third technique is to insert log statements liberally, so you can see what is going on.
It isn’t that easy to figure out what is going on when you run something as an application, I do believe however, that you can see what is going on, by opening the Console app, and choose all messages.
In your script that you run as an applet, insert log statments, like log “here”, and log “and here”, etc, to see where it fails.
You can of course have log statements within try on error end try blocks.
Are you sure that an Excel document was open when you ran script 2 ?
Would be fine to grab one more time the script from the message #5 before running it.
I wrote an other quick and dirty piece of code which extract some infos from the resources embedded in the info.plist of the application.
If you open with Excel the file which it creates on the Desktop,
in the 1st column you will get the list of file format recognized by Excel.
In column 2 & 3 you will get the type identifier associated to these formats.
script 3
(path to applications folder as text) & "Microsoft Office 2011:Microsoft Excel.app:Contents:Info.plist"
tell application "System Events"
property list file result
property list item "CFBundleDocumentTypes" of result
set theRecords to value of item 1 of result
end tell
set typeNames to {}
count theRecords
repeat with i from 1 to count theRecords
set aRecord to item i of theRecords
try
set end of typeNames to my recolle({} & CFBundleTypeName of aRecord & LSItemContentTypes of aRecord, tab)
end try
end repeat
(path to desktop as text) & "excel filetypes.txt"
my writeTo(result, my recolle(typeNames, linefeed), text, false)
--=====
on recolle(l, d)
local oTIDs, t
set oTIDs to AppleScript's text item delimiters
set AppleScript's text item delimiters to d
set t to "" & l
set AppleScript's text item delimiters to oTIDs
return t
end recolle
--=====
(*
Handler borrowed to Regulus6633 - http://macscripter.net/viewtopic.php?id=36861
*)
on writeTo(targetFile, theData, dataType, apendData)
-- targetFile is the path to the file you want to write
-- theData is the data you want in the file.
-- dataType is the data type of theData and it can be text, list, record etc.
-- apendData is true to append theData to the end of the current contents of the file or false to overwrite it
try
set targetFile to targetFile as text
set openFile to open for access file targetFile with write permission
if not apendData then set eof of openFile to 0
write theData to openFile starting at eof as dataType
close access openFile
return true
on error
try
close access file targetFile
end try
return false
end try
end writeTo
--=====
It use no fancy/third party tools.
Yvan KOENIG (VALLAURIS, France) dimanche 23 septembre 2012 22:45:37
Thanks for the explanation, it helps me understand a few more things. Specially about the log function.
Discussing this with one of the young people I work with they suggested i use the Automator function for converting old Excel files to the new format. It took me less than a minute and it works. Sometimes life is so simple…
and it gives the best answers really, though dictionaries, aren’t telling the full truth about how things really are at all times.