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 51

Finally, in AppleScript, since there is no need to set variables that are application references to null (our equivalent of Nothing), none of that needs to be prepared either. We're done. Quite a lot of syntactical differences between VBA and AppleScript for such a short script.

Delete a worksheet

In VBA, worksheets are deleted with the Delete method. In AppleScript, delete in the Standard Suite works the same way, except that it allows you to delete lists of several items all at once and avoid repeat loops completely in applications where this behavior is implemented. Note however, that workbooks must contain at least one worksheet, so you can‘t delete the last worksheet in a workbook. To delete all but the first sheet in VBA:

   On Error GoTo Err_Handler

Application.DisplayAlerts = False

For i = Worksheets.Count To 2 Step -1

Worksheets(i).Delete

Next i

Err_Handler:

Application.DisplayAlerts = True

As mentioned earlier, this backwards motion through repeat loops is always required in AppleScript in any case. So the way to do an exact replica would be:

tell application "Microsoft Excel"

     set display alerts to false

      try

          set allSheets to (every worksheet in active workbook)

          repeat with i from (count allSheets) to 2 by -1

              delete (item i of allSheets)

          end repeat

      on error

          set display alerts to true

      end try

end tell

But why bother? You're in AppleScript now, so do it the AppleScript way:

tell application "Microsoft Excel"

     set display alerts to false

     try

          delete items 2 thru -1 of (get every worksheet of active workbook)

      end try

      set display alerts to true

end tell

Much easier. (But note the explicit get needed.) The try statement is used because if there is in fact just one worksheet to begin with, there will be no item 2 and the statement will error. That is precisely the point you wish to be at (just one worksheet) so we move on and turn error messages (display alerts) back on. In fact, Excel even lets you do this without erroring:

tell application "Microsoft Excel"

     set display alerts to false

      try

          delete (every worksheet of active workbook)

     end try