WIN32::OLE Excel Pivot Table Grief

[Anonymous Monk]

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";
    }

 }

[strat]

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.

[Anonymous Monk]

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

[strat]

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});