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:
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.
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.
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: