Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: WIN32::OLE Excel Pivot Table Grief

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


in reply to WIN32::OLE Excel Pivot Table Grief

Turn on the Excel macro recorder and create the pivot table manually. Then stop the macro recorder, and view the makro code. You just have to translate it to Perl. If you need help with it, then show us the VBA code.

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


Comment on Re: WIN32::OLE Excel Pivot Table Grief
Re^2: WIN32::OLE Excel Pivot Table Grief
by Anonymous Monk on Nov 19, 2007 at 15:09 UTC
    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];

      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];

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (10)
As of 2014-12-29 14:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

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





    Results (190 votes), past polls