Re^3: Inserting copied rows at another position in Excel

[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

[davies]

Let me deal with your points in reverse order. The two types of insert are EntireRow (which is almost always what is meant) and what I frequently refer to as "Bill Gates Knows Best", which is the default if using the (pre-ribbon) command tree. Run the two snippets below and you will see the difference.

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

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

for my $col ('A'..'C') {
    for my $row (1..3) {
        my $rng = $col . $row;
        $sht->Range($rng)->{Value} = $rng;
    }
}

$sht->Range("B2")->Insert({Shift=>xlDown});

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

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

for my $col ('A'..'C') {
    for my $row (1..3) {
        my $rng = $col . $row;
        $sht->Range($rng)->{Value} = $rng;
    }
}

$sht->Range("B2")->EntireRow->Insert;

Notice that the "Shift $chars xlDown" is what appears in the VBA of the OP. That type of insert gets things out of alignment. It's sometimes useful provided you know exactly what is going on (it's effectively a move, another command I avoid unless its side effects are precisely what I want), but I hate it being the default.

On your second point, in this simple example, I would expect there to be no problem. But I don't know what Excel is doing internally. For example, does it regard the "entire row" as being limited to the used range? If it does, and between copying and pasting the code adds another column, I might get things out of alignment. This would certainly be an efficient way of coding, but of course we can't see the source code (not that I'd understand it).

On your first point, I'm not concerned whether there's an error. I expect I've made that sort of error - it didn't hurt my understanding as I don't use that form very often. But do you notice that in the post I'm replying to now you are relying on default behaviour? In my first code snippet, I use the line $sht->Range("B2")->Insert({Shift=>xlDown});, while your line follows an identical format without the shift option and the brackets. Does this default to an entire row? I don't know. But it's a nasty trap for someone wanting to insert a column, as Excel has two constants, xlRight (-4152) and xlToRight (-4161). Going down this route requires spending time reading documentation to be sure you are doing what you want. If you really want the "move" type inserts then you will have to do it or experiment to find which it is you need. But what if you get it wrong? This would be very easy when translating VBA to Perl. I expect I could find documentation telling me what the equivalents of xlDown are for entire rows and columns, but I'd rather just use the EntireWhatever version and know that my intention is clear to anyone who wants to maintain my code.

Regards,

John Davies

[HelenCr]

Thank you, John. This is instructive.

Helen