Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

WIN32::OLE Excel Pivot Table Grief

by Anonymous Monk
on Nov 19, 2007 at 01:57 UTC ( #651561=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to figure out how to turn off totals in an Excel pivot table generated by a perl script. Anybody any ideas? I'v tried three different approaches, so far without success. See the example code below. The problem is with the code at the end... none of it actually turns off totals. Thanks!!
use strict; use warnings; use Win32::OLE qw( with); use constant xlColumnField => 2; use constant xlCount => -4112; use constant xlDatabase => 1; use constant xlHidden => 0; use constant xlPivotTableVersion10 => 1; use constant xlRowField => 1; use constant xlSum => -4157; sub Macro1; my $_app_object = (Win32::OLE->GetActiveObject('Excel.Application') + || Win32::OLE->new('Excel.Application')); $_app_object->{'Visible'} = 1; $_app_object->Workbooks->Add(); $_app_object->ActiveSheet->Cells(1, 1)->{Value} = "a"; $_app_object->ActiveSheet->Cells(1, 2)->{Value} = "b"; $_app_object->ActiveSheet->Cells(1, 3)->{Value} = "c"; $_app_object->ActiveSheet->Cells(2, 1)->{Value} = "1"; $_app_object->ActiveSheet->Cells(2, 2)->{Value} = "2"; $_app_object->ActiveSheet->Cells(2, 3)->{Value} = "3"; $_app_object->ActiveSheet->Cells(3, 1)->{Value} = "4"; $_app_object->ActiveSheet->Cells(3, 2)->{Value} = "5"; $_app_object->ActiveSheet->Cells(3, 3)->{Value} = "6"; Macro1; sub Macro1 { $_app_object->ActiveWorkbook->PivotCaches->Add({SourceType => xlDa +tabase, SourceData => 'Sheet1!R1C1:R4C3'})->CreatePivotTable({TableDe +stination => 'Sheet1!R8C3', TableName => 'PivotTable1', DefaultVersio +n => xlPivotTableVersion10}); ##$_app_object->ActiveSheet->PivotTables('PivotTable1')->AddDataFi +eld($_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotField +s('a'), 'Sum of a', xlSum); $_app_object->ActiveSheet->PivotTables('PivotTable1')->AddDataFiel +d($_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields( +'a') ); { my $_with001 = $_app_object->ActiveSheet->PivotTables('PivotTa +ble1')->PivotFields('b'); with ($_with001, Orientation => xlRowField, Position => 2, ); } { my $_with002 = $_app_object->ActiveSheet->PivotTables('PivotTa +ble1')->PivotFields('a'); with ($_with002, Orientation => xlRowField, Position => 1, ); } { my $_with003 = $_app_object->ActiveSheet->PivotTables('PivotTa +ble1')->PivotFields('c'); with ($_with003, Orientation => xlColumnField, Position => 1, ); } #------------------------------------------- # The following code does not work... Help! #------------------------------------------- # Attempt 1 #---------- $_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields +('a')->{Subtotals} = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]; # Attempt 2&3 #------------ for (my $i=0; $i <= $#{$_app_object->ActiveSheet->PivotTables('Piv +otTable1')->PivotFields('a')->{Subtotals}}; $i++ ) { print "$i ".$_app_object->ActiveSheet->PivotTables('PivotTable1 +')->PivotFields('a')->Subtotals($i+1); print $_app_object->ActiveSheet->PivotTables('PivotTable1 +')->PivotFields('a')->{Subtotals}[$i]; ##$_app_object->ActiveSheet->PivotTables('PivotTable1 +')->PivotFields('a')->Subtotals($i+1) = 0; $_app_object->ActiveSheet->PivotTables('PivotTable1 +')->PivotFields('a')->{Subtotals}[$i] = 0; print " ".$_app_object->ActiveSheet->PivotTables('PivotTable1 +')->PivotFields('a')->Subtotals($i+1); print $_app_object->ActiveSheet->PivotTables('PivotTable1 +')->PivotFields('a')->{Subtotals}[$i]."\n"; } }

Comment on WIN32::OLE Excel Pivot Table Grief
Download Code
Re: WIN32::OLE Excel Pivot Table Grief
by strat (Canon) on Nov 19, 2007 at 07:25 UTC

    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"

      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"

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (16)
As of 2014-07-23 17:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (147 votes), past polls