Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Inserting copied rows at another position in Excel

by merrymonk (Hermit)
on Mar 01, 2011 at 10:54 UTC ( #890713=perlquestion: print w/replies, xml ) Need Help??
merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

I am using Excel 2007 on a windows XP PC.
I want to do the following in Perl
1. Select a number of rows
2. Copy these rows
3. Use the 'Insert Copied Rows' to get a copy of the select rows in another position of the spreadsheet
I have recorded an Excel VBA to do this and it is (I liked someone describing these as incantations.)
Rows("1:9").Select Selection.Copy ActiveWindow.SmallScroll Down:=30 Range("A39").Select Selection.Insert Shift:=xlDown
I do not think I need the ActiveWindow.SmallScroll Down:=30 because that is just a record of moving to the new insertion point.
I have found the following about converting VBA to Perl but it does not seem to really cover this set of actions

If you record a macro in Microsoft Office, this can often be translated directly into Perl. In Visual Basic for Applications (VBA) the syntax is like this:
object.method(argument).property = value
In Perl this becomes
object->method(argument)->{property} = value;
So for example this code from VBA:
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
becomes this in Perl:
$Chart->Axes(xlCategory, xlPrimary)->{CategoryType} = xlCategoryScale;

Below is the Perl code where I opened an existing spreadsheet and tried insert a number of copied rows
use strict "vars"; use OLE; use Win32::OLE::Const "Microsoft Excel"; use Win32::OLE::Variant; my($excel, $spsh_full, $workbook, $sheet, $selection); #___ DEFINE EXCEL $excel = CreateObject OLE "Excel.Application"; #___ MAKE EXCEL VISIBLE $excel -> {Visible} = 1; #___ OPEN EXISTING WORKBOOK $spsh_full = <full path to spreadsheet>; $workbook = $excel -> Workbooks -> Open($spsh_full); $sheet = $workbook -> Worksheets('Quotation'); $selection = $sheet->Rows("1:9"); print "$selection\n"; $sheet->Range("A39")->$selection->Insert;
The print for $selection gave OLE=HASH(0x183fab8)
However, it gave a failure on the last line which said
Can't call method "Insert" on an undefined value at line 16.
What change do I have to make to be able to copy the rows that I want?

Replies are listed 'Best First'.
Re: Inserting copied rows at another position in Excel
by davies (Parson) on Mar 01, 2011 at 14:14 UTC
    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. :-)


    John Davies

      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.

      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).

        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.


        John Davies

Re: Inserting copied rows at another position in Excel
by Anonymous Monk on Mar 01, 2011 at 11:20 UTC
      I looked at the references but as far as I can see they do not show an exmaple of where the selected rows are inserted at another position given by a Range (or perhaps Rows).
      It would be good to know if my $selection statement does what I hoped it would do - that is the equivalent of selecting the rows sepecified.
      As another trial I did try
      $sheet->Range("A39") = $selection
      but this gave the following error
      Can't modify non-lvalue subroutine call at insert_rows.pz line 16.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://890713]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (8)
As of 2018-07-17 10:00 GMT
Find Nodes?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?

    Results (363 votes). Check out past polls.