FortranMan
Level 1
(0 points)
Q: Applescript to convert from AW6 spreadsheets to Excel
I have an iMac (iMac12,2) running OS 10.9.1 with applications of Pages’09 (4.3), Pages (5.0.1), Numbers’09 (2.3) and Numbers (3.0.1) available.
One of my laptops is a MBA (MacBookAir1,1) that I have kept at 10.6.8 with applications Appleworks (6.2.2) and Appleworks (6.6.9) available.
Recently, I converted many CWWP documents from AW6 into Pages. This was done on the iMac with an Applescript that copied a file (AnyDocumentCWWP) to the desktop as a file named AWV6, opened AWV6 with Pages’09, saved the result as AWV6.Pages, applied the creation date and modify dates of the original to AWV6.Pages, moved the new AWV6.Pages to the original directory and moved the original file to the trash, renamed AWV6.Pages to the original filename (excluding extension). The Pages version of the original document does not preserve the formatting of the CWWP, but the content was now discoverable with Spotlight. Since I was primarily concerned with the content, I would consider the conversion effort successful enough.
I performed the same sort of process on the iMac with CWSS documents converting from AW6 into Numbers. Here the results were less successful. I discovered that many of the converted spreadsheets had cells where the cell contained the last calculated value in substitute for the formula originally present -or- Numbers was unable to open the CWSS spreadsheet due to size limitations. Neither problem occurs when the file is converted on the MBA by Appleworks into an Excel format. For those CWSS files that did not properly convert to Numbers, I would like to perform a conversion to Excel.
When a given spreadsheet is manually opened on the MBA with either version of Appleworks, one can “Save as” using several formats of Excel. That operation will preserve the formula which cannot be recognized in Numbers’09. An example of this issue is when a spreadsheet has a cell that contains a formula with the function “ISNUMBER”. When I manually open a CWSS in Appleworks (either 6.2 version), I can Save As Excel Win 97 … or Excel Mac 98 … creating an XLS file that preserves the formula.
I have attempted two techniques with Applescript to be able to convert my CWSS documents into XLS format. Neither seem to work.
The first technique is very similar to a few lines extracted from a canned script found on http://macscripter.net/viewtopic.php?id=8296 the relevant code is:
set theSprTranslator to "Excel Win 97, 2000, XP 2002 spr"
tell application "System Events" to set mfpath to get path of disk item ("~/desktop/" as string)
set newfile to (mfpath & "AWV6" as string)
set NewFilename to AWV6XLS
tell application "AppleWorks 6"
activate
open newFile -- alias reference is ok
save using translator theSprTranslator with NewFilename and replace
close front document without saving
end tell
When this did not work, I attempted a scripted GUI solution as a second option. The GUI code I had found at http://stackoverflow.com/questions/17348326/applescript-to-save-an-appleworks-do cument-wont-compile-end-tell-vs-end-easy is:
tell application "System Events"
tell process "AppleWorks"
click menu item "Save As…" of menu "File" of menu bar 1
click menu button "File Format" of window "Save : AppleWorks 6"
click menu item "Excel Mac 5 spreadsheet" of menu 1 of menu button "File Format" of window "Save : AppleWorks 6"
keystroke return
end tell
end tell
Unfortunately there is no “File Format” menu button, after a bit of further research, I discovered UI Browser and wrote my own Applescript equivalent:
set mytempwork1 to AWV6
set theSprTranslator to “Excel Win 97, 2000, XP 2002 spr”
tell application "Finder" to open file (mytempwork1 as string) of the desktop
tell application "System Events"
tell process "AppleWorks"
click menu item "Save As…" of menu "File" of menu bar 1
tell text field 1 of window "Save : AppleWorks 6"
set value to "XX" & mytempwork1
end tell
click pop up button 1 of group 1 of window "Save : AppleWorks 6"
click menu item theSprTranslator
—click radio button 2 of group 1 of window "Save : AppleWorks 6"
—delay 3
—click radio button 1 of group 1 of window "Save : AppleWorks 6"
end tell
end tell
tell application "System Events"
tell process "AppleWorks"
delay 10 — <<<<<IF YOU CLICK ON THE BUTTON WHILE IN THIS DELAY, THE SCRIPT WILL BEHAVE PROPERLY
click button "Save" of window "Save : AppleWorks 6"
delay 1
end tell
end tell
This GUI script behaves almost as I would like. It seems to ignore the setting of the “pop up button 1” unless I actually intervene while the script is running and I click on the button. When I do so, the script runs as intended.
Is there a way to force Appleworks to recognize the Applescripted value of the “clicked” menu item?
MacBook Air, Mac OS X (10.6.8)
Hiroto
Level 5
(7,348 points)
Hello
As far as I know, there's no need to resort to GUI scripting. The problem in exporting in AppleScript is that AppleWorks 6 only accepts typeChar (TEXT) data as translator name while AppleScript string has become always being represented as typeUnicodeText (utxt) since OS X 10.5. This makes passing translator name to save command difficult but there's a workaround method as shown in the sample code below. The |TEXT|() handler does the job. Tested with AppleWorks 6.2.4 under 10.6.8.
main() on main() set export_translator to "Excel Mac 98, 2001 spreadsheet" (* "Excel Mac 5 spreadsheet" "Excel Mac 98, 2001 spreadsheet" "Excel Win 5 spreadsheet" "Excel Win 97, 2000, XP 2002 spr" *) set export_translator_TEXT to my |TEXT|(export_translator) set src to (path to desktop)'s POSIX path & "in.cwk" set dst to (path to desktop)'s POSIX path & "out.xls" set srca to src as POSIX file as alias set dsta to touch(dst) as POSIX file as alias tell application "AppleWorks 6" --return export translators open srca tell document 1 if document kind = spreadsheet document then save in dsta using translator export_translator_TEXT end if close saving no end tell end tell end main on touch(f) do shell script "touch " & f's quoted form return f end touch on |TEXT|(u) (* string u : source text return data : «data TEXT...» representing u converted to MACROMAN encoding *) do shell script "u=" & u's quoted form & "; printf '%s' \"$u\" | iconv -f UTF-8 -t MACROMAN | xxd -p" run (run script " script «data TEXT" & result & "» end script") end |TEXT|
Hope this helps,
H
