Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re^2: WIN32::OLE Excel Pivot Table Grief

by Anonymous Monk
on Nov 19, 2007 at 15:09 UTC ( #651678=note: print w/ replies, xml ) Need Help??


in reply to Re: WIN32::OLE Excel Pivot Table Grief
in thread WIN32::OLE Excel Pivot Table Grief

The translated VB macro is below. However, this does not work when executed from perl, the totals/subtotals are not disabled. That's why I'm confussed. The VB version works, but the perl version does not...

$_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields +('a')->{Subtotals} = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];


Comment on Re^2: WIN32::OLE Excel Pivot Table Grief
Download Code
Replies are listed 'Best First'.
Re^3: WIN32::OLE Excel Pivot Table Grief
by strat (Canon) on Nov 20, 2007 at 07:56 UTC

    I think the error is earlier. If you add the following lines to your code, it will tell you where:

    use Win32::OLE::Const 'Microsoft Excel'; # instead of the constants Win32::OLE->Option( Warn => 3 );

    something seems to be wrong at CreatePivotTable

    $_app_object->ActiveWorkbook->PivotCaches->Add({SourceType => xlDa +tabase, SourceData => 'Sheet1!R1C1:R4C3'})->CreatePivotTable({TableDe +stination => 'Sheet1!R8C3', TableName => 'PivotTable1', DefaultVersio +n => xlPivotTableVersion10});

    Best regards,
    perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"

      Thanks for taking the time to help! I added the lines you suggested. There was one warning that I've fixed, but now I still get the same problem. I have made no changes to the other line you were suspecting. The error is
      OLE exception from "Microsoft Office Excel": Unable to set the Subtotals property of the PivotField class Win32::OLE(0.1704) error 0x80020009: "Exception occurred" in PROPERTYPUT "Subtotals" at c:\bin\tmp.pl line 54
      The pivot table code is reduced to
      $_app_object->ActiveWorkbook->PivotCaches->Add({SourceType => xlDataba +se, SourceData => 'Sheet1!R1C1:R4C3'})->CreatePivotTable({TableDestin +ation => 'Sheet1!R8C3', TableName => 'PivotTable1', DefaultVersion => + xlPivotTableVersion10}); $_app_object->ActiveSheet->PivotTables('PivotTable1')->AddDataField +($_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields(' +a') ); $_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields( +'a')->{Orientation} = xlRowField; $_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields( +'a')->{Position} = 1; $_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields( +'b')->{Orientation} = xlRowField; $_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields( +'b')->{Position} = 2; $_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields( +'a')->{Subtotals} = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
        use
        $_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields('a' +)->SetProperty( "Subtotals", 1, 0 );

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://651678]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (13)
As of 2015-07-29 21:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (269 votes), past polls