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 7

2. AppleScript in Microsoft Office (General)

Issues in converting VBA to AppleScript in Word, Excel and PowerPoint

On to the nitty-gritty. On the one hand, as mentioned, the Object Models of Word, Excel and PowerPoint are virtually the same in AppleScript as in VBA, with the names of the various terms being very similar as well. On the other hand, there are certain fundamentals of the AppleScript language that differ from the concepts behind VBA, necessitating differences in the implementation and also in the code you write.

This section will go over some of the differences, and also some of the near-equivalences that nevertheless appear somewhat differently, when "translating" from VBA to AppleScript in all the Office applications.

Note: when testing out scripts in Script Editor, you may wish to begin every one with an activate command on its own line just inside the tell application "Microsoft Word" (or "Microsoft Excel" or "Microsoft PowerPoint") block, as its first line. That will bring Word (or Excel or PowerPoint, as the case may be) to the front so you can see what is happening. It is not necessary to include activate when running saved scripts from the script menu while Word is in the front (and there may be other occasions when you are running a script from elsewhere and don't want Word to come to the front). I have generally not included activate in the sample scripts here, but you may wish to do so.

Tell the Application

Let‘s start with the most obvious contrast between VBA and AppleScript. VBA macros run within documents or templates of a particular application: Word, Excel or PowerPoint (on the Mac). AppleScripts are either self-standing applications or scripts run from the OS script menu (actually run by the background application “System Events”). So to call a command or access an object in Word, Excel or PowerPoint – or any other application whatsoever – you must direct your code to it in a 'tell' block:

tell application "Microsoft Word"

    save as active document file name myDocName file format format text

    -- all your other Word-specific code

end tell

If you don‘t frame the code within that tell block, it won‘t compile, let alone run:

save as active document file name myDocName file format format text

Click Compile and you get this error message:

            Syntax Error : A class name can't go after this identifier.

This is one of those obscure AppleScript errors. You‘ll note that 'active document' is selected behind the error message. That‘s because save and as are both built-in AppleScript language terms, whereas neither active nor active document are defined terms outside of a Word tell block. So the AppleScript compiler thinks that active is a variable (an "identifier"), and the keyword document (which is another built-in AppleScript language term) cannot follow a variable (the supposed active variable name) without some sort of appropriate preposition between them. E.g.,

save as active in document file name

would compile (before hitting another error at 'myDocName‘) although it doesn‘t mean anything outside Word and can‘t run: if you try to run it you get an

AppleScript Error: Can't make file name into type reference

error message. So always remember to include the 'tell' block when calling Word commands or classes.

Invoking one Office application from another in VBA is a non-issue in AppleScript

Note that it makes no difference in AppleScript whether your script is "primarily" for Word or, say, Excel. When calling Word, you simply tell application "Microsoft Word"; when calling Excel, you tell application "Microsoft Excel". Neither application is running the script.

So when you come upon part of an Excel macro, say, that goes to some trouble to try to get an existing instance of Word or to create a new one, e.g.:

On Error Resume Next

Set oWord = GetObject(, "Word.Application")

If Err Then

Set oWord = New Word.Application

WordWasNotRunning = True

End If

you can just cut all that out. Just end tell your Excel block and start a new tell application "Microsoft Word" block. If Word is not open, this instruction will open it.

The only decision you need to make is to decide whether you now want to bring Word to the front or not. If you do, make the first line of the Word block:

activate

That will bring it to the front if Word is already open (and if it isn't open, activate does not add anything, since Word would come to the front anyway, but it's just fine to include it ). If you don't want Word to come to the front but to stay in the background, make the first line of the Word block:

launch

That will open Word in the background if it isn't open yet, and will do nothing if it is already open in the background.

No Dots, No 'equals' Assignments

Well, you already know that AppleScript is not a "dot" language like VBA, other BASIC languages, Python, etc. Its "English-like", more verbose syntax uses of, with the word order reversed to show containment, attributes, etc.:

ActiveDocument.Paragraphs(1).Range.Text

becomes

tell application "Microsoft Word"

content of text object of paragraph 1 of active document
end tell

Note how an element (paragraph) can be specified by index (1) but using the singular paragraph, not Paragraphs as in VBA. (There are no Collection Objects in AppleScript: see below). And you don‘t use parentheses, just paragraph 1.

Some people used to "dot" languages, where you "drill down" from a top level object to the property you wish to access as opposed to AppleScript's usual method of "burrowing up" from the property to the top-level object, find the string of "ofs" irritating and are not aware that there is an alternative, the apostrophe-s syntax, in their preferred word order:

active document's paragraph 1's text object's content

But that tends to sound so awful (as English) that most people just use it one level deep (active document‘s paragraph 1, or text object‘s content) when the occasion arises.

A third way, and often very useful, is to tell an object to get some property or element:

tell active document to get its text object

and you can use nested tell blocks, like this:

tell application "Microsoft Word"

    tell active document

        tell paragraph 1

            tell text object

               get content

            end tell

        end tell

    end tell