http://qs321.pair.com?node_id=1159578


in reply to Win32::OLE PasteSpecial - why does xlPasteColumnWidths do not work

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]
  • Comment on Re: Win32::OLE PasteSpecial - why does xlPasteColumnWidths do not work
  • Download Code

Replies are listed 'Best First'.
Re^2: Win32::OLE PasteSpecial - why does xlPasteColumnWidths do not work
by woland99 (Beadle) on Apr 07, 2016 at 17:31 UTC
    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]

In Section Seekers of Perl Wisdom