Converting .xlx files in to xlsx not working

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

when I try to change

tell application "Microsoft Word"

to


tell application "Microsoft Excel"

it refuses to compile. What am I doing wrong?

Hi,

look into the dictionaries. The parameters of the save as command in Word an Excel are slightly different

The command you want is called “save workbook as”

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

Hello

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

Thanks for that, see also next post.

I tried to run this as a application but it does not work at all.

I was there, but could not find it. I did not know that Excel XML file format, is for xlsx files. Sorry learning all the time.

Behave flawlessly here.
I’m running under 10.8.2

Yvan KOENIG (VALLAURIS, France) dimanche 23 septembre 2012 18:59:16

The script 1 which I posted was a really scheme to get the info :rolleyes:

Yvan KOENIG (VALLAURIS, France) dimanche 23 septembre 2012 19:01:13

I am on SL so maybe that is why?

I got that I talk about script 2, that is not working.

Hello!

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, :slight_smile: 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.

log e & " : " & n

being particularily useful!

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

Morning McUsr

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…

It also has one for PPT to PPTX files.

Thanks for your help everybody.

Eric