http://www.perlmonks.org?node_id=651613


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"

Replies are listed 'Best First'.
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];

    [download]
[reply]
[d/l]

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

      [download]

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

      [download]

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

[reply]
[d/l]
[select]
        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

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

        [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom