set PTable to make new pivot table at PivotSheet with properties ¬
{source data:pivotSource, table range1:pivotDest, name:"Table1"}
add fields to pivot table PTable row fields {"A", "B"} with add to table
set the pivot field orientation of the pivot field named "C" of PTable to orient as data field
set the function of data field 1 of PTable to do sum
set the name of data field 1 of PTable to "Sum of C"
How would I tell Excel to suppress the subtotals on the row fields A and B (the equivalent of double-clicking them in the pivot table wizard and clicking “None”)? Microsoft’s documentation is annoyingly sparse in this area, and Google has been no help, either.
Thanks
-p
Model: PowerBook G4
AppleScript: 1.10.7
Browser: Safari 419.3
Operating System: Mac OS X (10.4)
I have posted a similar question recently regarding setting subtotals to “none” with Applescript and had no replies. I posted my question on the Apple.com, apple users forum and received a reply from Paul Berkowitz, who has written
various Applescripts and wrote the VBA to Applescript guide, that is on the Apple website. He sent me a script, which although on its own did not work, pointed me in the right direction. I have produced the following Applescript:
tell application “Microsoft Excel”
activate workbook
set pf to pivot fields of pivot table 1 of active sheet
try
set subtotals pf subtotal index 1 without value
end try
end tell
This Applescript gets the number of pivot fields in the pivot table and sets all subtotals to none. If you want to change only specific subtotals, you will need to reference them as follows:
tell application “Microsoft Excel”
activate workbook
set pf to pivot field "A" of pivot table 1 of active sheet
set subtotals pf subtotal index 1 without value
set pf to pivot field "B" of pivot table 1 of active sheet
set subtotals pf subtotal index 1 without value
end tell
Hey
how to create a pivot table that do both sum and count on the data field “C”? the default summarize function is “do sum”. The code is simalar like this:
set PTable to make new pivot table at PivotSheet with properties ¬
{source data:pivotSource, table range1:pivotDest, name:“Table1”}
add fields to pivot table PTable row fields {“A”} column fields {“B”} with add to table
set the pivot field orientation of the pivot field named “C” of PTable to orient as data field