Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 18

In case you skipped these when learning AppleScript, you should know about two very powerful features of AppleScript that can really speed up your scripts, and simplify them too. Most scriptable applications, fortunately including the Microsoft Office applications, implement both of them.

The first is the whose (or where) filter or, as the old Script Editor used to refer to it, specifying an element "by satisfying a condition".

tell application "Microsoft Word"

    every paragraph of active document whose content of text object contains "AppleScript"

end tell

gets me a list of 66 paragraphs in this document containing the string "AppleScript" in 2 seconds flat, without having to run a laborious repeat loop through every paragraph in turn. You can also ask for first paragraph (or last paragraph) too, but be sure to put that version in a try/on error block since it errors if there are none.

This is really incredibly powerful, works on virtually every element of every class in all the applications, and can save you great time and effort (if you're sure you've got the syntax correct!). I strongly recommend scrapping tedious repeat loops for your For, For Each, Do While VBA loops and substituting whose filters whenever possible when converting your macros. (The repeat loops seem to run a lot slower in AppleScript than in VBA, too – another very good reason.)

One gotcha to look out for: if the name of the property you're filtering on also happens to be the name of a class (e.g., category in Entourage, or replacement in Word) then use the synonym where its instead of whose: the important word its focuses AppleScript on the element being considered and its property of the equivocal name – otherwise AppleScript gets confused since the class name has priority when in doubt and you get an incorrect result of {} (no results).

An equally powerful feature for putting AppleScript to work and avoiding tedious repeat loops is asking for the (singular) property of every element:

tell application "Microsoft Word"

    name of every document

end tell

-->{"Document3", "MacPowerPointVBA_PunchList.doc", "Converting VBA To AppleScript in Microsoft Office.doc", "Document2"}

    name local of every Word style of active document
    --> {"1 / 1.1 / 1.1.1", "1 / a / i", "Article / Section", "Balloon Text", "Block Text", "Body Text", "Body Text 2", … etc

You get a list of the requested property for every element. Once again, there is no need to go through a tedious repeat loop through each element. (In the case of Word styles, that would be upwards of 165 elements. There could be hundreds or thousands of elements in other contexts.)

Now very occasionally you'll hit a bug with this method (e.g., I just hit one trying to get content of every word of text object of paragraph 1 of active document, where the bug is actually with every word, not with the content of property retrieval), but it's very rare: as long as you can get 'every element of some object' as a list you can get any of its properties in a list too.

Note that you cannot apply a whose filter to the resulting list, or any other AppleScript list (a failing of AppleScript many have waited long to see disappear, and may still yet someday). It‘s only applicable to application elements. What is odd is that most applications, including Entourage, allow you to combine the two features discussed here to get a property list on a whose filter, e.g.:

tell application "Microsoft Entourage"

    name of every contact whose default email address contains "microsoft.com"

end tell
--> {"Bill Gates", "Steve Ballmer", "Roz Ho"}

but you can't do the equivalent for Word, Excel or PowerPoint, e.g.:

tell application "Microsoft Word"

    name of every document whose content of text object contains "AppleScript"

end tell
--> missing value

That's a shame, but the two special features still give you a lot of added power on their own.

There's lots more to explore in AppleScript, but this should cover most of the essential points of convergence, and divergence, between the VBA and AppleScript ways of doing the same things in general terms, across the applications – especially if you read one of the AppleScript books before attempting your own scripts. Now it's time to look at some examples from the individual Microsoft Office applications.

Note: In the following chapters, the version of Office used is 2004, updated to 11.3.3, current at the time of writing. It is quite likely that many of the gaps and bugs mentioned in Office 2004's AppleScript, usually with workarounds provided, will be fixed in Office 2008. If you happen to be reading this after Office 2008 has been released, you may be pleasantly surprised to discover that some of the problems discussed no longer apply. Here's hoping!