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

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

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]

Comment on Re^4: WIN32::OLE Excel Pivot Table Grief Select or Download Code

Replies are listed 'Best First'.
Re^5: WIN32::OLE Excel Pivot Table Grief by Anonymous Monk on Feb 21, 2008 at 13:26 UTC
use `$_app_object->ActiveSheet->PivotTables('PivotTable1')->PivotFields('a' +)->SetProperty( "Subtotals", 1, 0 );` [download]	[reply] [d/l]
Re^6: WIN32::OLE Excel Pivot Table Grief by Anonymous Monk on Apr 05, 2009 at 04:07 UTC
Cool! That answer was worth waiting for. It works! Many Thanks!!!	[reply]
Re^6: WIN32::OLE Excel Pivot Table Grief by Anonymous Monk on May 13, 2016 at 02:01 UTC
Thank you very much. I was stuck on this "subtotals" for a day but your code did the trick Thank you.	[reply]

In Section Seekers of Perl Wisdom