Win32::OLE and creation of pivot table in Excel

Discussion in 'Perl Misc' started by Domenico Discepola, Dec 3, 2003.

  1. Hello once again. My goal is to create a pivot table in Excel using data
    from an existing worksheet in the current workbook. The worksheet is called
    'Test Worksheet' and the data resides in cells a1:c3. As I am new to
    Win32::OLE, I don't know how to "translate" the following VB Script
    (generated from a macro I recorded in Excel) into a "Win32::OLE" / perl
    version of those methods:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "'Test
    Worksheet'!R1C1:R2C3").CreatePivotTable TableDestination:="",
    TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

    ActiveSheet.Cells(3, 1).Select

    Any help would be appreciated.

    TIA
     
    Domenico Discepola, Dec 3, 2003
    #1
    1. Advertisements

  2. As I am new to
    OK - I read up on "Named Parameters" and found an old post with a concrete
    solution to my problem. I was able to generate the pivot table using the
    following code;

    $workbook->PivotCaches->Add( {SourceType => 1, SourceData => 'Test
    Worksheet!R1C1:R2C3'} )->CreatePivotTable( { TableDestination => "",
    TableName => "PivotTable1", DefaultVersion => 1 });

    You'll notice that the values of certain named parameters have been replaced
    with a number (SourceType and DefaultVersion). Does anyone know where can I
    read more on this (replacement of parameter values with numbers)?

    TIA
     
    Domenico Discepola, Dec 3, 2003
    #2
    1. Advertisements

  3. Hi Domenico,

    The ActiveState documentation has some pretty good examples, under "Using OLE with Perl", which is in the table of contents under "ActivePerl FAQ/Windows Specific".

    Try also
    perldoc Win32::OLE

    Cheers,
    Ben Liddicott
     
    Ben Liddicott, Dec 3, 2003
    #3
  4. Does anyone know where can I
    If you work with Perl under Windows you probably have ActiveState's
    distribution installed. This distribution comes with HTML documentation
    containing an 'OLE Browser' that can be found under 'ActivePerl
    Components / Windows Specific'.
    Unfortunately it works only with IE!
     
    Karlheinz Weindl, Dec 3, 2003
    #4


  5. OLE with Perl", which is in the table of contents >>under "ActivePerl
    FAQ/Windows Specific".

    Thank you for your replies. Unfortunately, I don't think I'm understanding
    the documentation on Win32::OLE. I've tried Activestate's, Roth's Book and
    Microsoft MSDN. My overall goal is to create an Excel worksheet with a
    customized pivot table. My code works except when I try to customize the
    pivot table:

    my $class = "Excel.Application";
    my $file = "c:\\temp\\p2.xls";
    my @arr01 = ( ["a", "b", "c"], [1, 2, 3] ); #simulate data
    my $r = my $c = 0;

    unlink( $file ) if ( -e $file ); #delete file if exists

    #Create application class called "$excel"
    my $excel = Win32::OLE->GetActiveObject( $class );

    if ( ! $excel ) {
    $excel = new Win32::OLE( $class, \&QuitApp ) || die "Could not create COM
    ${class} object\n";
    }

    $excel->{Visible} = 1;
    $excel->{SheetsInNewWorkbook} = 1;

    #create a new workbook
    my $workbook = $excel->Workbooks->Add();

    #create a new worksheet
    my $worksheet = $workbook->WorkSheets(1);
    $worksheet->{Name} = "gah";

    #populate the 1st worksheet with data from the array
    foreach ( @arr01 ) {
    for ( $c = 0; $c<3; $c++ ) {
    $worksheet->Cells($r+1, $c+1)->{Value} = $arr01[$r][$c];
    }
    $r++;
    }

    #Create the pivot table - hard coded references for now
    $workbook->PivotTableWizard( {
    SourceType => 1,
    SourceData => 'gah!R1C1:R2C3',
    TableDestination => "",
    TableName => "PivotTable1",
    HasAutoFormat => 1
    });

    #######################################
    # PROBLEM HERE
    #I want to assign cell "a1" as the pivot table's row field
    # since creating the pivot table automatically created a new worksheet, you
    must
    # reference the new worksheet
    #Sample VBScript code taken from MSDN website:
    # Worksheets("sheet3").PivotTables(1).PivotFields("year").Orientation =
    xlRowField

    $workbook->WorkSheets(2)->PivotTables(1)->PivotFields(1)->( {Orientation =>
    1});
    #######################################3

    $workbook->SaveAs( $file );
    $workbook->Close();
    exit 0;

    The error message I get is:
    OLE exception from "Microsoft Excel":
    Unable to get the PivotTables property of the Worksheet class
    Win32::OLE(0.1603) error 0x800a03ec

    I assume that this means that there's some kind of problem with the
    PivotTables property. I checked the Excel object model
    (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/ht
    ml/xltocObjectModelApplication.asp) and found that the PivotTables property
    does belong to the worksheet class.

    Any help would be appreciated.
     
    Domenico Discepola, Dec 4, 2003
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.