Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Troubles transposing a range in Excel with Win32::OLE

by guha (Priest)
on Jan 10, 2002 at 00:20 UTC ( #137553=perlquestion: print w/replies, xml ) Need Help??

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

Hi fellow monks, today I have spent some time trying to transponate a range (2D-grid) in Excel.
I have followed the usual(?) procedure to record a macro in excel doing the operation and then converting the VBA-code to perl

The line that does the actual transpose look like this

$sheet->Cells(10,5)->PasteSpecial( {Paste => xlAll, Operation => xlNone, SkipBlanks => 0, Transpose => 1 });
This results in the following errormessage from Win32::OLE
OLE exception from "Microsoft Excel": Could not find PasteSpecial property for the Range class Win32::OLE(0.15) error 0x80020009: "Exception occured" in METHOD/PROPERTYGET "PasteSpecial" at line 31

I get the same errormessage when I invoke the function with the more standard call approach

$sheet->Cells(10,5)->PasteSpecial(xlAll, xlNone, 0, 1);
HOWEVER when I skip the fourth argument, "Transpose", thing work OK except that no transposition is done as the argument defaults to false

Any ideas someone??

Replies are listed 'Best First'.
Re: Troubles transposing a range in Excel with Win32::OLE
by jmcnamara (Monsignor) on Jan 10, 2002 at 04:21 UTC

    It looks like "Transpose" requires a Variant agrument. The following works for me:
    use Win32::OLE::Variant; # ... $worksheet->Cells(10,5)->PasteSpecial( { Paste => xlAll, Operation => xlNone, SkipBlanks => 0, Transpose => Variant(VT_BOOL, 'True') });


      Problem Solved

      Should this be considered a bug in OLE ?
      Knowing that c-era could replicate the problem in VB, why was there no refs of this found in google ?
      Is Transpose such a rare creature or is it just me ??

      Kudos to jmcnamara and c-era!


        I don't think that this is a bug.

        However, it is often hard to match Perl's variable types with those expected by VBA. That is why the Win32::OLE::Variant module is so useful.

        A good resource for Win32::OLE related information is the ActiveState mailing lists. In particular the perl-win32-users list. Jan Dubois, the author of Win32::OLE, often answers questions there.

        As an aside, Win32::OLE is an extremely powerful module and potentially very useful. However, it is let down by a lack of comprehensive documentation and examples.


Re: Troubles transposing a range in Excel with Win32::OLE
by c-era (Curate) on Jan 10, 2002 at 01:34 UTC
    I tried it on my pc, and got the same result. I'm running win2k, office 2k, and perl 5.6.1. I then tried using VB to write the code, and wasn't able to make the call either. I'm guessing something isn't right with the ole object. You can always transpose the vaules yourself. If you use my $range_values = $sheet->Range("A1:C5")->{'Value'}; range_values will be a reference to a 2d array. Simply flip the array and write the results with $sheet->Range("D1:F5")->{'Value'} = $range_values; Let me know if you find anything else out.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://137553]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2022-05-18 12:45 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (70 votes). Check out past polls.