Re: Inserting copied rows at another position in Excel

[davies]

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

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add;
my $sht = $wb->Sheets(1);

my $rngFrom = $sht->Range("A1:A9")->EntireRow;
my $cellCount = $rngFrom->Cells->Count;
for (1..100) {
    my $cellNo = int(rand($cellCount))+1;
    $rngFrom->Cells($cellNo)->{Value} = rand;
}

my $rngToTopLeft = "A39";
my $rngIns = $sht->Range($rngToTopLeft . ":" . 
                         $sht->Cells($sht->Range($rngToTopLeft)->Row +
+ $rngFrom->Rows->Count -1,
                                     $sht->Range($rngToTopLeft)->Colum
+n)->Address);
$rngIns->EntireRow->Insert;
$rngFrom->Copy($sht->Range($rngToTopLeft));

Your first requirement is wrong. You say, "I want to do the following in Perl: 1. Select a number of rows". You don't. Trust me, you don't. Nor do you want to activate a worksheet. Not now, not ever. I'll try, some time over the next few days, to put up a rant in Meditations describing the pitfalls of selecting and activating and showing the only time when I have found it necessary. The simple version, though, is "Don't". Update: rant now posted at Excel’s Select and Activate considered harmful

The first nine lines of my code just set up an Excel instance, book and sheet. I then define my "From" range using "EntireRow". This is pretty much synonymous with your way, but I want to expose you to EntireRow, as it has advantages I will explain later. Lines 11 to 16 can be ignored as they just populate 100 cells with random numbers.

Line 17 is pretty obvious, but lines 18 to 20 are a little strange at first sight. I am setting up a range of cells, starting at the target top left, one column wide and as many rows deep as there are rows in the source range. This involves a little symbolic juggling that you can accept with thanks or explore if you are interested.

Lines 20 and 21 are the meat, though. What you tried to do was copy in one command and paste in another, combining the insertion of rows with the pasting. I have done it differently, first inserting explicitly and then copying and pasting in a single line. There are several reasons for this. The first is that I find the code more intuitive. The second is that I always prefer to insert explicitly. This is because there are two ways of inserting rows, EntireRow and what I refer to as "Bill Gates Knows Best" mode. It is probably paranoia, but I am always scared that someone will change or copy my code to another application and will not end up inserting entire rows, which is almost always what is intended.

An important trap to avoid, though, is copying to $rngIns. Because you have inserted, this range has moved down, so you would be leaving the new rows blank and overwriting any data in the rows that were in the target range!

So there you have it. Not a Select in sight. :-)

Regards,

John Davies

[merrymonk]

Very many thanks for both the code and detailed explanation.
I will look for your 'rant in Meditations' in the hope that it will help me to understand more about controlling Excel with Perl.

[HelenCr]

Davies: isn't the following better (simpler and more elegant)? Starting from the 4th line from the bottom in your code:

my $rngToTopLeft = "A39";
$sht->$rngFrom->Copy;
$sht->$rngToTopLeft->Insert;

The main advantage is, that you don't need to exactly calculate the destination range (a calculation that may be error-prone and a hassle).

[davies]

Simpler, possibly, but I don't find it more elegant. As I said, I always prefer to insert explicitly. This may be paranoia on my part, but I have seen spreadsheets go horribly wrong when the wrong insert method is used (compare the first two code examples in RFC Tutorial - Deleting Excel Rows, Columns and Sheets. Inserting and deleting have the same two - incompatible - options). There are spreadsheets I wrote over 20 years ago still used live even though they were written in 123, and I prefer to keep things very explicit. Can you be absolutely certain that, in Excel 2033, that $sht->$rngToTopLeft->Insert; of yours will work in exactly the same way? Probably you can. But with two types of insert currently available, I want readers of my code to be certain that I really mean the EntireRow type. It's worth noting that, in the OP, MerryMonk's recorded VBA uses the other type of insert.

As I say, this is probably paranoia. I'm certainly not going to the stake over it. But I avoid your insert command because, without testing, I can't be absolutely sure which insert is used or whether it is consistent from one version of Excel to another.

Regards,

John Davies

[HelenCr]

Thank you, Davies.

Firstly, I think I made a mistake, my last code line should go:

$sht->Range($rngToTopLeft)->Insert;

instead of what I wrote.

Second, about the "EntireRow" method, since, in your code, you have

my $rngFrom = $sht->Range("A1:A9")->EntireRow;

then aren't you assured that the insertion will be entire rows?

and thirdly, can you detail what you mean by "two types of Insert"?

Many TIA - Helen