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 2

1. AppleScript Basics

Tools, Resources and First Steps in AppleScript Syntax

If you are a proficient AppleScripter just looking for tips on converting Office VBA-to-AppleScript, you can skim or skip most of this section aside from the Microsoft Office AppleScript Resources section below.

Script Editors

If you have never done any AppleScripting, you first need to know where to write your scripts. Go to /Applications/AppleScript folder, and launch Script Editor. That‘s it.

Script Editor is a fairly basic IDE (though immeasurably better than the earlier version found in Classic OS 8 and 9 and in the early versions of OS X up to 10.2.8). If you find after a while you want something better, take a look both at the free editor Smile <http://www.satimage.fr/software/en/downloads_software.html>, an advanced script editor which is somewhat unusual and has a rather steep curve, and the commercial Script Debugger <http://www.latenightsw.com/sd4/download.html> which has got one of the very best IDEs for any programming language (outstripping any version of Visual Basic Editor you may know) for features and intuitive ease of use, and is available in a free time-limited Trial Demo version.

These articles require, and are illustrated with only the Script Editor built-in to OS X. They will use OS 10.4 Tiger, but all examples would be identical in OS 10.3 Panther and are most likely to be the same also in OS 10.5 Leopard (which Apple has not yet released at the time of writing). There is no difference between using a new Intel Mac or an older PPC Mac when developing or running AppleScripts. Using Office 2004, all commands to the Office apps are carried out on Intel Macs in Rosetta in any case, although calls to other applications such as the Finder and to built-in scripting additions will be native Intel. When the next version of Mac Office is released, commands will run native to the platform they‘re executing on.

(Note: all code in this document was written for Office 2004. Some of it could require changes, as well as opportunities for possible optimization, if some of the terminology changes in Office 2008.)

Learning AppleScript

AppleScript was designed to be easy to read – "English-like" is the proverbial expression. It‘s not so much easier to write, however, than any other programming language, and it‘s got its own conventions. Beginners to programming or scripting tend to appreciate the somewhat "colloquial" syntax, while many people accustomed to programming in other languages – VBA coders, you are warned! – tend to get annoyed at occasional lack of predictability. There are often several different ways of saying the same thing, but that doesn‘t mean that just any way you can dream up will work. It‘s probably best just to stick to the recommended canonical way: you can‘t go wrong.

Now let‘s face it: you‘re never going to be able to write AppleScript until you learn it. If you can write VBA code, or any other programming language, it won‘t take you long at all, since almost all the concepts and constructs are familiar; maybe a few days or weeks to get fluent in the basics. There are now many books on AppleScript.

The two extremes of approach might be represented by AppleScript:The Missing Manual by Adam Goldstein (O‘Reilly), a primer to bring you up to speed quickly with the basics, but you‘ll have to dip into another reference such as Matt Neuburg‘s book when you hit an issue not dealt with there, and AppleScript:The Definitive Guide, 2^nd Edition by Matt Neuburg (O‘Reilly) which is the very best book on AppleScript as a language and the perfect one for people who already know another programming language such as VBA. For a step-by-step approach, especially if a beginner, choose between the most complete and exhaustive book (some 808 pages) AppleScript: The Comprehensive Guide to Scripting and Automation in Mac OS X, 2^nd Edition by Hanaan Rosenthal (Apress), or a beginner‘s approach Beginning AppleScript by Stephen G. Kochan (Wrox), or the most compact all-purpose guide Danny Goodman‘s AppleScript Handbook, Mac OS X Edition by Danny Goodman. (If you get into Finder scripting, Ben Waldie‘s useful AppleScripting the Finder eBook is available from SpiderWorks.) Finally, the old AppleScript Language Guide, a free PDF on the Apple website, although written for OS 8.6 and outdated now, is still mostly accurate as far as it goes.

In addition, MacTech Magazine regularly runs AppleScript articles that range from beginning AppleScript to advanced AppleScript.

To follow along with these articles, you should not need any of them, but to actually get into writing scripts and translating VBA macros to AppleScript yourself, you will need to familiarize yourself with AppleScript syntax, and should get one or more of these books. URLs for ordering or downloading them, as appropriate, can be found in the Resources chapter at the end of this document.

Office AppleScript

Now, assuming you know, or can follow basic AppleScript syntax, what about scripting the Office apps? Well, if you know Office VBA, you‘re just about there already. The AppleScript 2004 object models of Word, Excel and PowerPoint are, for all intents and purposes, identical to that of their respective VBA object models.

They really are the same object model – with a few differences endemic to the differing structures of the two languages, as described in detail in the next section. The equivalent AppleScript terms are all lower cased and can consist of several words, as opposed to VBA‘s portmanteau capitalized MultipleWordsSquishedTogether: e.g., Word VBA‘s AutoTextEntry Object becomes the auto text entry class in AppleScript.

Because the object model provides a great many arguments to Methods in VBA (which become parameters to commands in AppleScript) plus a great many enumerated constants (all those wdWhatever and xlThisOrThat constants) as values for these arguments/parameters, this can make for quite a mouthful in AppleScript, where all these words run into each other without punctuation. Thus

   ActiveDocument.SaveAs FileName:=myDocName, FileFormat:=wdFormatText

in VBA becomes in AppleScript:

save as active document file name myDocName file format format text

Here the command name save as is followed by its direct object active document without any punctuation, and then the two parameters file name and file format are each followed by a value: in the case of the file name by a variable myDocName (clearly seen to be a variable due to the different green formatting than the blue used by Script Editor for application keywords*), in the case of file format one of the enumerated values available to it, namely format text. But all running on without punctuation.

(*These colors are the default formatting styles in Script Editor, as used throughout this document. You can change any or all of the styles – their font, size and/or color – representing the eight different syntactical elements in AppleScript Formatting by going to Script Editor / Preferences / Formatting.)