|Perl Monk, Perl Meditation|
Re: Inserting copied rows at another position in Excelby davies (Parson)
|on Mar 01, 2011 at 14:14 UTC||Need Help??|
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. :-)