Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

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

by HelenCr (Monk)
on Jan 12, 2013 at 14:49 UTC ( #1013045=note: print w/ replies, xml ) Need Help??

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

Thank you, Davies.

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


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

Comment on Re^4: Inserting copied rows at another position in Excel
Select or Download Code
Replies are listed 'Best First'.
Re^5: Inserting copied rows at another position in Excel
by davies (Parson) on Jan 12, 2013 at 16:23 UTC

    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.


    John Davies

      Thank you, John. This is instructive.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1013045]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (12)
As of 2016-04-29 19:43 GMT
Find Nodes?
    Voting Booth?
    :nehw tseb si esrever ni gnitirW

    Results (441 votes). Check out past polls.