6 Replies Latest reply: Dec 29, 2012 1:54 PM by Steven Jones
Steven Jones Level 2 Level 2 (185 points)

For years I have been using AppleWorks spreadsheets on my Quadra (ease-of-use, nostalgia, old habits die hard, etc.). After eventually deciding to transfer some of them to my G5 and work with them (as well as creating new ones) in a newer and faster environment, I started fiddling around with using AppleScript to automate some of the repetitive tasks within each document, especially for the new ones. One task appears to have either no solution or a particularly well-hidden one. Except for the example given here, all the other scripting is working to my satisfaction.

 

The relevant part of the script is:

 

tell application "AppleWorks 6"

    activate

    make new document at front with properties {document kind:SS}

 

    tell spreadsheet of document 1

        select menu item "Display…" of menu "Options"

    end tell

 

end tell

 

The line "select menu item "Display…" of menu "Options" brings up a window as pictured here.

Picture 1.jpg

 

I would like to be able to use AppleScript to uncheck the checkboxes labelled "Row Headings" and "Column Headings" so that the whole script (of which this is only a small part), can run without ANY manual intervention. So far, no luck. The arrow keys and tab key (with and without modifier keys) have no effect within this window. If you hold down the command key for a a few seconds, the window displays command-key equivalents for the checkboxes, as pictured here;

Picture 2.jpg

Turning on GUI scripting by enabling access for assistive devices in System Preferences and using calls to System Events with various permutations of the "keystroke" command had no effect. To try to discover if the items in the window were actually UI elements and of what type, I used the "get entire contents of window ... " approach, which worked beautifully in the main document window. The calls I used, inserted after the line ""select menu item "Display…" of menu "Options" and before the two "end tell" lines, were:

 

tell application "System Events"

     tell process "AppleWorks 6"

 

          get entire contents of window 1          -- each of these four lines,

          get entire contents of window 2          -- and other various similar attempts, was used by itself

          get every item of window 1               -- (all included consecutively here to save space)

          get every UI element of window "Display"

 

     end tell

end tell

 

The line "get entire contents of window 1" only took effect once the "Display" window had been dismissed, and then it quite happily returned the contents of the main document window. The others returned either: "System Events got an error: NSReceiverEvaluationScriptError: 4" or "System Events got an error: Can't make every item of window 1 of process "AppleWorks 6" into type reference."

 

My guess is that the window "Display" is a modal dialog which is not letting anything else happen until it is dismissed. In the script, the next command after "select menu item "Display…" of menu "Options" waits until the window has been dismissed.

 

I have also tried saving the document (once the row and column headings have been hidden) as a spreadsheet template. That ALMOST achieved what I wanted. The new spreadsheet template shows up in Starting Points as "Spreadsheet" with the previous (pre-defined) template being renamed to "Standard SS". When clicking on the new template in Starting Points, a new SS document opens up with the row and column headings hidden, but when a new SS document is created using AppleScript, it uses the pre-defined template (headings not hidden). So near yet so far!

 

If anybody has any suggestions, tricks and workarounds for both AppleScript and AppleWorks, I am willing to try them out.

 

If I can't get this (small) part of the script to work without having to use the mouse or keyboard, then it's not the end of the world. But I do like sitting back and watching the computer do what it is good at - repetitive tasks, etc. - from start to finish.

 

- - -    break for food and thought   - - -

 

While not having found the reason for AppleScript's inability to access the "Display" window, I have worked out a way to achieve what I want. Even though the AppleScript command "make new document at front with properties {document kind:SS}" causes AppleWorks to ignore the customized template document and use the original, pre-defined one, the following routine makes a copy of the customized template document, converts it from a stationery document (template), renames it and opens it.

 

tell application "Finder"

 

     set TemplateCopy to duplicate file "AppleWorks SS Options.cws" of folder "Templates" of folder "Starting Points" of folder "AppleWorks 6" of folder "Applications" of startup disk to desktop

 

     set stationery of TemplateCopy to false

     set NewFileName to "My spreadsheet.cwk"

     set the name of TemplateCopy to NewFileName

     open file NewFileName

 

end tell

 

One other approach which might have led somewhere was to explore the ability to prevent row and column headings from printing, even if they are displayed on screen. Scripting the print end of things may not have been easy, if possible at all, and would have required much more complexity in scripting. Also, I would prefer the screen display to match what is going to be printed.

 

The end result I wanted has been achieved, although using a different approach than I expected. Given the legacy status of AppleWorks 6 and its oddities of behaviour with AppleScript (even though much functionality was added and many bugs fixed from version 5), I doubt that the original approach will ever work, although if anybody has some ideas/knowledge, etc., I am willing to listen.


PowerMac, Mac OS X (10.4.11), Quad Core 2.5GHz G5
  • 1. Re: AppleScript & AppleWorks Spreadsheet
    Pierre L. Level 4 Level 4 (3,860 points)

    Steven Jones wrote:

     

    […]

     

    I have also tried saving the document (once the row and column headings have been hidden) as a spreadsheet template. That ALMOST achieved what I wanted. The new spreadsheet template shows up in Starting Points as "Spreadsheet" with the previous (pre-defined) template being renamed to "Standard SS". When clicking on the new template in Starting Points, a new SS document opens up with the row and column headings hidden, but when a new SS document is created using AppleScript, it uses the pre-defined template (headings not hidden). So near yet so far!

     

    […]

     

    So  why don't you just open the spreadsheet template instead of creating a new document using AppleScript?

     

    For example:

     

    set myTemplate to POSIX file "POSIX/path/to/the/new/spreadsheet/template" as alias

    tell application "AppleWorks 6"

        activate

        open myTemplate

    end tell


  • 2. Re: AppleScript & AppleWorks Spreadsheet
    Steven Jones Level 2 Level 2 (185 points)

    Hi Pierre,

     

    I had tried opening the spreadsheet template but it made a copy of itself in the Templates folder and then opened the copy - that's why I included the bit about making a copy on the desktop and converting the copy from a stationery document.

     

    When I pasted your example into Script Editor (inside or outside of a Finder tell block), the first line automatically turned into:

     

    set myTemplate to file ":POSIX:path:to:the:new:spreadsheet:template" as alias

     

    I'm guessing that this behaviour is because I am running OS X 10.4.11.

     

    As I am not experienced in using POSIX files and paths, I had to fiddle around a bit before settling on the following code to achieve the same result. I have deliberately avoided using the name of the hard drive as I like my AppleScripts to be as portable as possible, so that if I rename my hard drive or move the scripts to a different computer then there will be a minimum of rewriting to be done in terms of where things are and what they are called. There are probably better ways of writing this, but it works and speed is not an issue:

     

    tell application "Finder"

        set PathToAppsFolder to path to applications folder

        set myTemplate to ((PathToAppsFolder & "AppleWorks 6:Starting Points:Templates:AppleWorks SS Options.cws") as text) as alias

    end tell

     

    tell application "AppleWorks 6"

        activate

        open myTemplate

    end tell

     

     

    Thanks for your input.

  • 3. Re: AppleScript & AppleWorks Spreadsheet
    Pierre L. Level 4 Level 4 (3,860 points)

    The simplest way to write the script seems to be as follows:

     

    set myTemplate to (path to applications folder as text) & "AppleWorks 6:Starting Points:Templates:AppleWorks SS Options.cws" as alias

     

    tell application "AppleWorks 6"

        activate

        open myTemplate

    end tell


    I have tested that script under Mac OS X 10.4.11, but could not see that the spreadsheet template made a copy of itself in the Templates folder and then opened the copy.

  • 4. Re: AppleScript & AppleWorks Spreadsheet
    Steven Jones Level 2 Level 2 (185 points)

    Great!

     

    Both versions work the same, but the simplfied one certainly is neater.

     

    Thanks again.

  • 5. Re: AppleScript & AppleWorks Spreadsheet
    Steven Jones Level 2 Level 2 (185 points)

    Scripting printing in AppleWorks 

     

    As a spin-off from my efforts described above, I think I have found a way of using AppleScript to save documents from AppleWorks as PDF's. At the moment the script is pretty wordy and maybe not as efficient as it could be, but it works (almost 100% of the time).

     

    In the Print dialog, the tab key can be used to move between the various elements in the dialog. Depending on which one has been arrived at after each press of the tab key, you can select and do various things by using the space key, up and down arrow keys and the return key. I'm not sure if they can all be consistently emulated using the keystroke command from within System Events, as here I have restricted myself to just the tab, down arrow and return keys.

     

    When the Print command is issued (via keystroke "p" using command down), the Print dialog opens up.

    Picture 1.jpg

    On my system, the number of copies is highlighted by default when the Print dialog opens and by pressing the tab key five times the PDF button is selected (although it's not all that obvious). The down arrow then activates the dropdown menu - press it again and the first item in that menu is highlighted. Press Return and that item is accepted and the Save dialog comes up.

    Picture 2.jpg

    On my system it takes one press of the tab key with shift down to move "back" from the Save As text field to the Save button (originally I had been using ten presses of tab in the "forward" direction), at which point Return selects it and the document is saved as a PDF. As with the Print dialog, the arrow keys, space key and Return can all be used to do/select various things along the way.

     

    In the script I have inserted a number of delays to give things time to happen. Originally they were longer and allowed me to observe what was being selected at each emulated press of the tab key. Those that are one second or longer need, at least on my system, to remain fairly long otherwise I get errors such as "connection not valid" and the script hanging to the point where the only way out was to force quit. I would assume that  some things are timing-dependent and need a specific amount of time before the next action can be performed. If anybody tries my script and finds odd things happening, I would suggest lengthening the delay times as the first thing to try. Also, on various System versions, there might be a difference in the number of tab presses required to move from the default highlighted attribute to the PDF and Save buttons.

     

    The script as it stands at the moment requires at least one AppleWorks document to be open (and checks for that to avoid an error at the start). It could be modified to be a droplet (and possibly open only one document at a time from those dropped onto it, as AppleWorks would have a limit of how many documents it could have open concurrently), but I will get around to doing that later on.

     

    The script itself:

     

    tell application "AppleWorks 6"

        activate

     

        set HowManyDocs to count of documents

        if HowManyDocs > 0 then

            set DocNames to name of every document

     

            repeat with CounterA from 1 to HowManyDocs

                try

                    set ThisDoc to (item CounterA of DocNames)

                    select document ThisDoc

                    tell application "System Events"

                        tell process "AppleWorks 6"

                            keystroke "p" using command down -- brings up Print dialog

                            delay 0.1

                            tell window "Print"

                                delay 1 --  or longer

                                repeat with CounterB from 1 to 5

                                    keystroke tab -- tabs through to PDF Button

                                    delay 0.1

                                end repeat

     

                                key code 125 -- down arrow - selects PDF dropdown menu           

                                delay 0.1

                                key code 125 -- down arrow - selects first item in PDF dropdown menu

                                delay 0.1

                                keystroke return -- accepts selection and brings up Save dialog

                                delay 1

                                keystroke tab using shift down -- tabs back 1 to Save button

                                delay 0.1

     

                                keystroke return -- "presses" Save button

                                delay 2 --  or longer

                            end tell

                        end tell

     

                    end tell

                end try

                close document ThisDoc -- closes each document after it is "printed" to PDF

            end repeat

     

        else

            display dialog "You have no AppleWorks documents open." & return & "Open some and then run this script again."

        end if

     

    end tell

  • 6. Re: AppleScript & AppleWorks Spreadsheet
    Steven Jones Level 2 Level 2 (185 points)

    If you do try this script out, please be very aware that it does not do any error-checking except for making sure that at least one AppleWorks document is open. If an error dialog pops up reporting that, for example, your page margins are too small for the printer then this script does not know how to handle that and any mouse clicks or keys pressed by you in response to that dialog - it simply runs through its instructions, which currently do not include handling problems.