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 46

   ActiveWorkbook.Worksheets.Add Before:=1, Count:=1

In AppleScript you would do that this way:

make new worksheet at beginning of active workbook

To add two worksheets to the right of the last worksheet:

With ActiveWorkbook.Worksheets

   .Add After:=.Item(.Count), Count:=2

End With

That's a little more complicated for AppleScript, since there is no way to make two or more elements at a time: make new makes just one element. However, we do have a lot of options for inserting elements. Here's how it works:

The proper ways in AppleScript of indicating an insertion location are: at beginning of, at end of, at before [some element], at after [some element]. But they do not always all work: they only do so if implemented by the application developers. What works here is at beginning of, at end of, but not reliably with before or after. (Well, 'before' appears to work if you don't refer to any element (i.e. an existing sheet) as you're supposed to, and just write make new worksheet at before active workbook; but all that's happening is that 'before' is being ignored and the worksheet is made at the default location at the beginning. If you try it 'at before sheet 3' for example, it works once but then crashes if the new sheet 3 is not the active sheet – a peculiar bug that may have something to do with faulty re-indexing on Excel's part. )

For the particular VBA example here, we do not even have to count the sheets – just make a new sheet at the end, twice:

     repeat 2 times

          make new worksheet at end of active workbook

     end repeat

Suppose you already have four sheets, and you want to insert two more after the first one (at the left) and have no idea which sheet is the currently active one. You might try using after (but don't omit the at as well, which sounds peculiar, but is correct). However, if you try it, as seems logical:

tell application "Microsoft Excel"

     tell active workbook

          repeat 2 times

              make new worksheet at after sheet 1

          end repeat

     end tell

end tell

Excel crashes on the second repeat (that bug referred to above: sheet 1 is not active after the first time through the repeat). It does not seem possible to create new sheets in the middle by AppleScript directly – you'll have to add sheets and the beginning or end only. But with a bit of ingenuity you can do it, using the Standard Suite's move command. (Excel's own copy worksheet command was needed to translate VBA's Copy Method to AppleScript in order to get before and after parameters, but the Standard Suite's move command already has a to parameter that allows before and after insertions. The developers did the right thing in making use of move. But it's easy to forget to look in the Standard Suite.)

Here's how to insert two new worksheets after Sheet 1:

tell application "Microsoft Excel"

     tell active workbook

          repeat 2 times

              set ws to make new worksheet at end

              move ws to after sheet 1

          end repeat

     end tell