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 44

end tell

omitting the Dim statement, of course, because there is none in AppleScript.

The one optional argument for the Workbooks.Add method, the Template argument to open a new workbook based on a particular template, has no equivalent template parameter to the make new command. And there is no template property for workbooks to set at inception, either.

But there is a very simple solution that works perfectly. Unlike Mac Word 2004 and earlier, opening an Excel template – whether from Excel, or in the Finder, or via VBA, or via AppleScript – opens a new document (workbook) made from the template. So instead of make new document, just open the template. Thus

   Application.Workbooks.Add Template:="Mac HD:Folder:Template.xlt"

in VBA should be converted to:

tell application "Microsoft Excel"

     open "Mac HD:Folder:Template.xlt"

end tell

and that's all there is to it. If you need to set a variable (reference) to the new workbook, then instead of the basic open command from the Standard Suite – which does not return a result – use Excel's own open workbook command from the Excel Suite:

tell application "Microsoft Excel"

     set newWkbk to open workbook workbook file name ¬

          "Mac HD:Folder:Template.xlt"

end tell

This is the better solution.

Open an existing workbook

And that brings us around to how to open an existing document – which we've just done! As we can see, there are two ways to do it. All applications implement the Standard Suite's open command – it's one of the most basic commands there is, and is required of all Mac applications, even those otherwise unscriptable: open, run, print and quit are all required. Sometimes applications add their own specialized parameters to open, as Word does.

The problem is, that to keep it as flexible as possible, since it can also be used both by unscriptable applications and by the Finder to open any application's documents without having any conception how to represent them when opened, it does not return a result. That is not good for scriptable applications that open their own documents.

Excel does the smartest thing: it leaves the Standard Suite's open command in place – where it can be used to open entire lists of files at once – but with no special parameters. Instead, it also has a separate open workbook command in the Microsoft Excel Suite, equivalent to VBA's Open, with all of its parameters (arguments), and which does return a result. You can therefore set a variable (reference) to the open workbook command. That's the one you should use.

Workbooks.Open Filename:="Mac HD:Folder:File.xls"

translates to:

tell application "Microsoft Excel"

     open alias "Mac HD:Folder:File.xls"

     -- or:

     open "Mac HD:Folder:File.xls"

     -- or:

     open workbook workbook file name "Mac HD:Folder:File.xls"

end tell

while

   Set oWkbk = Workbooks.Open(Filename:="Mac HD:Folder:File.xls")

translates to:

tell application "Microsoft Excel"

     set theWkbk to open workbook workbook file name "Mac HD:Folder:File.xls"

end tell

The open workbook version offers many options. To open a text file you might find a macro with the Format argument, specifying the delimiter (1=Tab, 2=Comma, 3=Space, etc):

Workbooks.Open FileName:="Mac HD:Folder:File.txt", Format:=1

which translates to:

open workbook workbook file name ¬