Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

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

by Gangabass (Vicar)
on Apr 05, 2016 at 05:38 UTC ( #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( ); }
  • 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();

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1159578]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2018-07-22 14:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?















    Results (454 votes). Check out past polls.

    Notices?