I am using Excel 2007 on a windows XP PC.
I want to do the following in Perl
1. Select a number of rows
2. Copy these rows
3. Use the 'Insert Copied Rows' to get a copy of the select rows in another position of the spreadsheet
I have recorded an Excel VBA to do this and it is (I liked someone describing these as incantations.)

Rows("1:9").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=30
Range("A39").Select
Selection.Insert Shift:=xlDown
[download]

I do not think I need the ActiveWindow.SmallScroll Down:=30 because that is just a record of moving to the new insertion point.
I have found the following about converting VBA to Perl but it does not seem to really cover this set of actions

If you record a macro in Microsoft Office, this can often be translated directly into Perl. In Visual Basic for Applications (VBA) the syntax is like this:
object.method(argument).property = value
In Perl this becomes
object->method(argument)->{property} = value;
So for example this code from VBA:
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
becomes this in Perl:
$Chart->Axes(xlCategory, xlPrimary)->{CategoryType} = xlCategoryScale;

Below is the Perl code where I opened an existing spreadsheet and tried insert a number of copied rows

use strict "vars";
use OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE::Variant;
my($excel, $spsh_full, $workbook, $sheet, $selection);
#___ DEFINE EXCEL
$excel = CreateObject OLE "Excel.Application";
#___ MAKE EXCEL VISIBLE
$excel -> {Visible} = 1;
#___ OPEN EXISTING WORKBOOK
$spsh_full = <full path to spreadsheet>;
$workbook = $excel    -> Workbooks -> Open($spsh_full);
$sheet    = $workbook -> Worksheets('Quotation');
$selection = $sheet->Rows("1:9");
print "$selection\n";
$sheet->Range("A39")->$selection->Insert;
[download]

The print for $selection gave OLE=HASH(0x183fab8)
However, it gave a failure on the last line which said
Can't call method "Insert" on an undefined value at insert_rows.pl line 16.
What change do I have to make to be able to copy the rows that I want?

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.