Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

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

by strat (Canon)
on Nov 20, 2007 at 07:56 UTC ( #651880=note: print w/ replies, xml ) Need Help??


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

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"


Comment on Re^3: WIN32::OLE Excel Pivot Table Grief
Select or Download Code
Re^4: WIN32::OLE Excel Pivot Table Grief
by Anonymous Monk on Nov 21, 2007 at 01:42 UTC
    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 );
        Cool! That answer was worth waiting for. It works! Many Thanks!!!

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (10)
As of 2015-07-07 11:33 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 (88 votes), past polls