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 52

      set display alerts to true

end tell

Without even telling it to do so, it leaves the first worksheet in place without trying to delete it nor erroring. Well, that's a handy shortcut to use now that you know about it, but don't count on it in all other circumstances.

Deleting a whole list of items, like opening a whole list of items, is a great and powerful advantage of AppleScript, especially in production work: use it.

In all cases, set the application property display alerts to false to get around Excel's annoying "safety feature" of a separate Alert window for each worksheet about to be deleted. Then turn alerts back on when done.

Protecting worksheets

Worksheet protection exists to prevent inadvertent changing of cells by the user. (It is not a security method, as the protection can be bypassed in seconds by freely available methods.) Here's how it's done in VBA:

   ActiveSheet.Protect Password:="drowssap"

In AppleScript there are separate commands protect worksheet and protect workbook, depending on what you're protecting, so:

tell application "Microsoft Excel"

     protect worksheet (active sheet) password "drowssap"

end tell

Note that the converse, unprotect, works on both sheets and workbooks:

     unprotect (active sheet) password "drowssap"

and there is a minor error in the dictionary: although it correctly shows that the command has a direct parameter (sheet/workbook) the formatting in the dictionary for the latter indicates that these are enumeration constants, but in fact they are not: they actually indicate the class of the object required: sheet or workbook. More on this issue, which crops up in several places, at the end of this chapter.

To easily protect all sheets in a workbook using the same password:

Const sPWORD As String = "drowssap"

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

   ws.Protect Password:=sPWORD

Next ws

Once again, you can scrap the looping:

set sPWORD to "drowssap"

protect worksheet (every worksheet in active workbook) password sPWORD

It's not just Standard Suite commands like open and delete that can act on multiple items: so do protect worksheet, unprotect, and many others in the Excel dictionary: Excel has evidently implemented most of its own commands to do so too. But you will need to check with every command new to you, since sometimes you will hit one that does not do so.

Hiding worksheets

Worksheets can be hidden from the user. To hide all worksheets with "(hide)" in their name:

Const sHIDEINDICATOR As String = "(Hide)"

Dim ws As Worksheet

For Each ws In Worksheets

   ws.Visible = Not (LCase(ws.Name) Like _

             LCase("*" & sHIDEINDICATOR & "*"))

Next ws

Another easy one for AppleScript:

tell application "Microsoft Excel"

     repeat with ws in (get every worksheet in active workbook)

          set visible of ws to not (name of ws contains "(Hide)")