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?

In reply to Inserting copied rows at another position in Excel by merrymonk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.