Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
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
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 imbibing at the Monastery: (6)
As of 2014-12-20 22:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (99 votes), past polls