Actually, what you describe is quite simple, provided you know the precise incantation.

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents} = 0;
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $sht = $wb->Sheets(1);
$sht->{Name} = "RVG_Engines";

$sht->Cells(1,1)->{Value} = "Title Row";
$sht->Cells(2,1)->{Value} = "Data 1";
$sht->Cells(3,1)->{Value} = "Data 2";
$sht->Cells(4,1)->{Value} = "Data 3";

$sht->Cells(2,1)->EntireRow->Insert;

[download]

I'm taking it that the difference between "Data1" and "Data 1" in your two data examples is just noise. If you need to know how to change that, please reply saying so.

I haven't used your code as a basis, as there are several things in it that appear to have been cut out, such as an assignment to $outxls. Without them, the code can't run. If you aren't using strict and warnings, it's worth doing. You use $sheet->Activate. This appears liberally in transliterated VBA, but it comes from recorded macros recording everything, useful or not. Activating sheets and selecting cells is something code very rarely needs to do. If you don't know why it's there, it's usually safe to cut it out.

Unless you are sure what you are doing, it can be dangerous to put spaces in file or sheet names. It would be easy for me to construct a trap that would cause you problems. Perl is less susceptible than VBA, but if you can persuade yourself and your users to avoid spaces, your life is likely to be easier. Underscores are fine.

I'm far from clear what you are doing with $lastrow. You seem to be calculating it in a complicated and unreliable way, and then not using it. By doing a search, you don't know what you will get if the bottom right cell of the used range doesn't match the search. Assuming A1, A2 and B1 are occupied, will your search return A2 or B1? $sht->UsedRange->Rows->Count will give you the last row in the used range, but that may not be what you want. There's a lot of literature on the precise nature of the used range, so I would need to know what you are after to advise better.

$lastrow += 1 can be written more Perlishly as $lastrow++.

Unless you are changing $outxls in the code you have cut, you don't need to SaveAs. $workbook->Save would do the job more elegantly. You might not need to change DisplayAlerts, although it's something I routinely do in my initialisation code. I haven't closed Excel after me, as I would want, in your place, to see what had happened.

Regards,

John Davies

In reply to Re: Perl OLE Excel - Insert Row by davies
in thread Perl OLE Excel - Insert Row by nleach

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.