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 58

     tell sheet "Sheet1" of workbook "Book2.xls"

          paste worksheet destination range "J3"

     end tell

end tell

If you‘re only trying to copy values, it‘s far more efficient and quicker to simply assign them, rather than calling the copy range command (Copy method in VBA):

With Sheets("Sheet1").Range("A1:J10")

   Sheets("Sheet2").Range("B10").Resize( _

          .Rows.Count, .Columns.Count).Value = .Value

End With

becomes:

tell application "Microsoft Excel"

     tell (range "A1:J10" of sheet "Sheet1")

          set value of (get resize (range "B10" of sheet ¬

              "Sheet2" of active workbook) row size (count rows) ¬

              column size (count columns)) to get its value

     end tell

end tell

In this case it is necessary to specify of active workbook for sheet "Sheet2" since it is in a tell block targeted at a range in sheet "Sheet1", a different sheet. AppleScript is perhaps more objected-oriented here than VBA in that if you don't specify of active workbook it looks for Sheet 2 inside its own range "A1:J10", of course doesn't find a sheet there, so doesn't do anything.

Similarly, you absolutely need the explicit get for its value to force it to be evaluated in the same command. (If you set a variable to it in a previous line then the get is not necessary: see Chapter 2. Actually Excel seems not nearly so insistent on the explicit get as Word, but in this case it is necessary.) The its may not be essential this time but it helps keep straight whose value (the original range's) we're talking to. And frequently it is necessary in tell blocks.

Paste special

The paste special command in the Table Suite (there is a different paste special on worksheet command in the Excel Suite which is not concerned with ranges) corresponds to the PasteSpecial method in VBA, and allows you to perform operations on the destination range based on the contents of the clipboard (e.g., add, subtract, multiply, divide).

For instance to multiply the value in each cell of a range by 2:

Application.ScreenUpdating = False

' Get the value in the clipboard using a

' cell in a temporary worksheet

With ActiveWorkbook.Worksheets.Add

   With .Cells(1, 1)

      .Value = 2

      .Copy

   End With

End With

Sheets("Sheet1").Range("A1:J10").PasteSpecial _

      Paste:=xlPasteAll, _

      Operation:=xlPasteSpecialOperationMultiply

' Delete the temporary worksheet

With Application

   .DisplayAlerts = False

   ActiveSheet.Delete

   .DisplayAlerts = True

   .ScreenUpdating = True

End With

Here it is in AppleScript:

tell application "Microsoft Excel"

     set screen updating to false -- speed it up