Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

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

by davies (Parson)
on Jan 12, 2013 at 16:23 UTC ( #1013049=note: print w/replies, xml ) Need Help??

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

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

Replies are listed 'Best First'.
Re^6: Inserting copied rows at another position in Excel
by HelenCr (Monk) on Jan 15, 2013 at 19:14 UTC

    Thank you, John. This is instructive.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1013049]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (8)
As of 2017-06-23 15:05 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (549 votes). Check out past polls.