Excel Automation Example

You want to find out how to write VB/VBA code to copy the range of cells A1-C1 to A3 in a spreadsheet. You don't know anything about the Excel Automation interface.

Start Excel, then start the Inspector. In Excel, put some text in cells A1 and C1. Expand the "Microsoft Excel" tree node on the left panel, then expand "Properties", which should look similar to shot on the right.

Click on the "Show Documentation" link next to "Application" at the bottom, and you will get this:

In looking at the properties, you see a bunch that start with "Active". ActiveSheet looks good. Open that up and you see a Cells property. Check that by clicking the documentation link, and you see that it returns a Range, and you can see that its type is a Range.

The help for Range is pretty informative, let's see if we can get a Range. You click on the Range property further down in the ActiveSheet object, and you fill in the values in the Parameters panel to get the desired range. Then hit Get Prop to populate the Range object with the selected range.

Lets search the Excel Type Library for things related to copy. Click on the Type Library link at the bottom, and select Edit/Find. Here are the results of the search:

There is a Copy method on Range (you assume that IRange is the interface for the Range object). Go into the methods for Range and select Copy, then click Show Documentation.

Syntax 1 is what we want, we can copy to the clipboard and then copy from the clipboard into the desired Range. Invoke the Copy Method on Range, by selecting Copy and clicking on Invoke Method.

Look at the spreadsheet and you can see that the Range has been put on the clipboard:

Then go back to the Range property of the ActiveSheet and enter A3 and C3 to specify the Range of the destination row. Now we need to find out how to get the clipboard contents to this Range. Looking through the methods on Range, we see PasteSpecial. Clicking on the Show Documentation yields:

Select the PasteSpecial method and you see these parameters:

Since we just want the values and we don't want any special operations, set the parameters to this:

And invoke the method. You will see this in the spreadsheet: