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 43

Normally in AppleScript, you expect to be able to specify many, or most, of the properties of a workbook in a with properties parameter, right at creation time, as in Chapter 2's example of making a new table at a document. (A scripter would expect that only elements, if any are needed, would have to be added afterwards.) That turned out not to be possible with Word documents, but it is possible with Excel, I am happy to report.

All of the properties listed in the dictionary as read/write, with the exception of those that require that a workbook first be set up as a shared workbook, can be set with properties {…} in the make new command. In VBA, you would have to set these after making the new workbook and setting a reference to it in a variable.

You can do that in AppleScript, too, as we will see below, but you can also set any of the following properties at inception. I have set all of them here to something other than the default values, and they all work, including setting passwords:

tell application "Microsoft Excel"

     set newWkbk to make new workbook with properties ¬

          {accept labels in formulas:false, conflict resolution:¬

              local session changes, date 1904:¬

              false, display drawing objects:¬

              placeholders, personal view list settings:¬

              false, personal view print settings:¬

              false, precision as displayed:true, save link values:¬

              false, update remote references:¬

              false, template remove external data:¬

              true, keep change history:¬

              true, remove personal information:¬

              true, password:"yyyyy", write password:¬

              "zzzzz", read only recommended:¬

              true, workbook comments:"Hi, there"}

end tell

You can verify that these are non-default settings, if you wish, by first running

      properties of active workbook

on a regular blank new workbook, copy the result to a text editor, and then run it again after making another workbook using the script above with non-default property values. Compare the results. If you save and then re-open the workbook, you will be asked for passwords, and all the other properties, including the ones recorded in Excel/Preferences/Calculation/Document Properties are all set already.

Setting properties at inception in the make new command is a useful time-saver, and something to consider when you start writing your own AppleScripts in the future. Probably when converting your VBA macros, it is simpler to just mimic the standard VBA macro line by line:

   Dim newWkbk As Workbook

Set newWkbk = Workbooks.Add

With newWkbk

.AcceptLabelsInFormulas = False

.PrecisionAsDisplayed = True

' etc.

End With

In AppleScript:

tell application "Microsoft Excel"

     set newWkbk to make new workbook

     tell newWkbk

          set accept labels in formulas to false

          set precision as displayed to true

          -- etc

     end tell