woland99 has asked for the wisdom of the Perl Monks concerning the following question:

Hi - I write a script that populates Excel spreadsheet with data from Lotus Notes. I would like to have template for the output file stored as a simple Excel file - single worksheet containing header only (20+ columns, 4 rows).
Then I would like to open additional worksheet and copy that header from first sheet to all others - preserving column formatting.
Here is my code to copy header: 
my $wsheet = $wbook->Worksheets(1);
my $last_row_from_OLE = $wsheet->{UsedRange}->Rows()->Count();
my $last_col_from_OLE = $wsheet->{UsedRange}->Columns()->Count();
my $range = $wsheet->Range($wsheet->Cells(1,1), $wsheet->Cells($last_r
+ow_from_OLE, $last_col_from_OLE) );
$range->Copy();
my $xlPasteColumnWidths = 8;
$range->PasteSpecial({Paste => $xlPasteColumnWidths});

[download]
Then I loop through tab names, add new tab and copy the header: 
foreach my $tab_name (@tab_names[1..(scalar  @tab_names - 1)]){
   my $wsheet2 = $wbook->Worksheets->Add( {after => $wbook->Worksheets
+($wbook->Worksheets->{count})} );
   $wsheet2->{Name} = $tab_name;
   $wsheet2->Paste();
}

[download]
The header is pasted in but without preserving column widths.
What am I doing wrong? TIA for any pointer/info.

Replies are listed 'Best First'.
Re: Win32::OLE PasteSpecial - why does xlPasteColumnWidths do not work
by Gangabass (Vicar) on Apr 05, 2016 at 05:38 UTC
    This code works for me: 
    my $wsheet            = $wbook->Worksheets(1);
my $last_row_from_OLE = $wsheet->{UsedRange}->Rows()->Count();
my $last_col_from_OLE = $wsheet->{UsedRange}->Columns()->Count();
my $range             = $wsheet->Range( $wsheet->Cells( 1, 1 ),
    $wsheet->Cells( $last_row_from_OLE, $last_col_from_OLE ) );
$range->Copy();
my $xlPasteColumnWidths = 8;

foreach my $tab_name ( 1 .. 3 ) {

    my $wsheet2 = $wbook->Worksheets->Add(
        { after => $wbook->Worksheets( $wbook->Worksheets->{count} ) }
+ );
    $wsheet2->{Name} = $tab_name;

    my $range2 =
      $wsheet2->Range( $wsheet2->Cells( 1, 1 ), $wsheet2->Cells( 1, 1 
+) );
    $range2->PasteSpecial( { Paste => $xlPasteColumnWidths } );
    $wsheet2->Paste( );
}

    [download]
[reply]
[d/l]
      Thanks a lot - that works. I was under impression that PasteSpecial needs to be called on source range and not target one. As an added bonus I can now preserve BOTH formulas and column width: 
      foreach $tab_idx (1..3) {
   $wsheet_out = $wbook_out->Worksheets($tab_idx);
   $wsheet_out->{Name} = $tab_names[$tab_idx - 1];
    
   $range->Copy();
   #to preserve formulas and column widths
   my $range_out = $wsheet_out->Range($wsheet_out->Cells(1,1), $wsheet
+_out->Cells(1,1));
   $range_out->PasteSpecial({Paste => $xlPasteFormulas});
   $range_out->PasteSpecial({Paste => $xlPasteColumnWidths});
   $wsheet_out->Paste();

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom