Ankit.11nov has asked for the wisdom of the Perl Monks concerning the following question:


I have an existing excel spread sheet. I want to copy some of the rows in the same Sheet multiple times.

I have gone through this Tutorial also Using Win32::OLE and Excel - Tips and Tricks (http://www.perlmonks.org/?node_id=153486) but the solution given here doesnt seems to work. 
$range1=$sheet->range('A17');
$Sheet->copy($range1);
$range2=$sheet->range('B15');
$range2=$sheet->paste();

[download]
Any help will be appreciated.

Replies are listed 'Best First'.
Re: Copy paste rows in excel
by Corion (Patriarch) on Jul 21, 2009 at 06:33 UTC
[reply]
      The Solution given at Using Win32::OLE and Excel - Tips and Tricks is not working. But its not giving any failure also.
      Thanks for pointing to Macro Reader link i will try that.
[reply]
Re: Copy paste rows in excel
by CountZero (Bishop) on Jul 21, 2009 at 06:47 UTC
    but the solution given here doesnt seems to work
    That is a pretty vague statement. You will learn that you get much better answers if your question is more precise.

    Now we can only guess.

    <crystal ball mode = ON>

    Did you use strict; use warnings;? If you did you should have gotten an error for $Sheet->copy($range1); because $Sheet is not the same variable as $sheet.

    <crystal ball mode = OFF>

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

[reply]
[d/l]
[select]
      That was just a typing mistake here. I was using use strict & use warnings in my program. 
      use strict;
use warnings;
use Win32::OLE;

#get an existing app or create a new one
my $ex = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OL
+E->new('Excel.Application' , 'Quit');

$ex->{DisplayAlerts}=0;

#open workbook
my $book = $ex->Workbooks->open('D:\Profiles\in2228c\Desktop\TIN\Cr_an
+alysis.xls');

#Activating worksheet
my $sheet = $book->Worksheets("Sheet1");

my $range1=$sheet->range('A1');
$sheet->copy($range1);
my $range2=$sheet->range('A2');
$range2=$sheet->paste($range2);

$book->SaveAs('D:\Profiles\in2228c\Desktop\TIN\Cr_analysis.xls');

      [download]
[reply]
[d/l]
Re: Copy paste rows in excel
by imrags (Monk) on Jul 21, 2009 at 07:17 UTC
    Well, i wrote the following and it worked on my system.
    use Win32::OLE;

my $excelfile = 'E:\\perl scripts\\abc.xls';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{'Visible'} = 1;
my $Book = $Excel->Workbooks->Open($excelfile);     

my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate();    
#------Here's what u were looking for   
$Sheet->range('A4')->copy();
$Sheet->range('B1')->Select();
$Sheet->paste();

    [download]
    With what I've learnt using excel<->perl, i've found that one should write the code
    as one would work manually with excel (step by step), that helps with perl/excel.
    That's my feeling anyway!
    Raghu
[reply]
[d/l]
      Thanks Raghu for the help. With this piece of code copy operation is working for me as well.
      One more clarification:
      Is there any way in which we can copy one complete row and paste it, as here it just picks the cell A4 and copies to cell B1.
[reply]
        As I said, think how you'll do it manually and make perl do it. 
        
$Sheet->rows('4:4')->copy();
$Sheet->rows('15:15')->Select();
$Sheet->paste();

        [download]
        The above code copies 4th row cells to 15th row cells.
        Raghu
[reply]
[d/l]

Back to Seekers of Perl Wisdom