nleach has asked for the wisdom of the Perl Monks concerning the following question:

What I thought to be simple has turned out to be far from it. I’m using Win32::OLE together with SOAP::Lite, and just trying to insert a blank line into an existing spreadsheet at row two.

My general syntax is OK, as I’m successfully writing to the XLS, and formatting data, but I cannot find away of simply inserting an empty row at row two.

Eg I have 

Title Row
Data1
Data2
Data3

[download]

And I would like

Title Row

Data 1
Data2
Data3

[download]

I’m doing stuff like this to write data 

 
$excel    = CreateObject OLE "Excel.Application";

    $workbook = $excel    -> Workbooks -> Open("$outxls");
    $sheet    = $workbook -> Worksheets(1) -> {Name};
    $sheet    = $workbook -> Worksheets($sheet);
    $sheet                -> Activate;


    $sheet->{Name} = "RVG Engines";

    my $lastrow = $sheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row}
    if $sheet->UsedRange->{Count}>1;

    $lastrow += 1;
    $sheet -> Range("A$lastrow") -> {Value} = $date;
    $sheet -> Range("B$lastrow") -> {Value} = $daemons;
    $sheet -> Range("C$lastrow") -> {Value} = $engines;
$excel->{DisplayAlerts} = 0;
    $workbook->SaveAs($outxls);
    $excel->Quit

[download]
Very many thanks for any help, Cheers Nigel

Replies are listed 'Best First'.
Re: Perl OLE Excel - Insert Row
by davies (Monsignor) on Jan 17, 2011 at 17:33 UTC

    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

[reply]
[d/l]
[select]

      Very many thanks for the swift and detailed reply John. I use Win32::OLE rarely, and coming from a Unix background, my problem is that it seems neither intuitive, or easy to look up how to do something you haven’t done before. That may well be my own limitation though.

      You’re correct that there is much missing from the snippet; I should have probably included less, to make that more obvious. I appreciate all your other comments and will be looking into them tomorrow in the office.

      Cheers, Nigel
[reply]

Back to Seekers of Perl Wisdom