Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re: Win32::OLE PasteSpecial - why does xlPasteColumnWidths do not work

by Gangabass (Vicar)
on Apr 05, 2016 at 05:38 UTC ( [id://1159578]=note: print w/replies, xml ) Need Help??


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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1159578]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2024-04-18 05:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found