Hi, this is probably something simple but can anyone explain to me why trying to make a new pivot table throws up an error? The error is “can’t make class pivot table”. I’ve tried several different ways, but I’ve found an example online that should work but it doesn’t.
http://lists.apple.com/archives/applescript-users/2009/Jan/msg00218.html
set PTRows to {"code", "projectNumber", "brand", "projectDescription", "size", "product", "upc", "currentTask", "taskOwner", "ActualStartDate", "comments", "Project Status"}
set PTFields to {"Team", "projectType"}
set PTTableName to "PivotTable3"
tell application "Microsoft Excel"
activate
set CWB to make new workbook
set the name of the active sheet to "Data"
set newsheet to make new worksheet at the end of CWB
tell newsheet
set name to "Pivot Table"
end tell
set PTSheet to the sheet named "Pivot Table"
--creates Pivot Table
set pTable to make new pivot table at PTSheet with properties ¬
{source data:range "Data!A1:BA29999", table range1:range "", name:PTTableName}
set small grid of pTable to false
add fields to pivot table pTable row fields PTRows page fields PTFields with add to table
--sets PivotTable Row Fields to Subtotal of None
repeat with i in PTRows
set pf to pivot field i of pivot table PTTableName of active sheet
set subtotals pf subtotal index 1 without value
end repeat
set the pivot field orientation of the pivot field named "currentTask" of pTable to orient as data field
end tell
Bumping because I’m still struggling with this. Can anyone please help? I’ve tried everything I can think of and it still doesn’t work. All I need to do is create a pivot table. I’m beginning to think that applescript cannot create them.
Thanks for any help whatsoever.
Hopefully you’re still checking for responses. I’m hitting this same wall. I figured out how to get Excel to create the pivot table minimally, but I haven’t cracked the code for getting data into it.
The trick for getting it to work in the first place is that it looks like the “make new pivot table” statement must be accompanied by a source data property. As long as you provide a source data property, presto, there’s a pivot table.
Then you can use the “add fields to pivot table” command to add row and column headers.
That command does NOT work for adding data fields to the table, though. So my example below gets tantalizingly close without actually really doing anything useful for you.
But it’s a start. Working together, I’ll bet we can puzzle through the rest of it.
I’m running this on a system running Mac OS X 10.6.3 and Excel 2008 version 12.2.3.
Here’s the code for creating the pivot table and adding some starter row and column fields:
(for this to work, you’ll need to create a worksheet named “Data” that contains columns headed “Row Field 1”, “Column Field 1”, and “Column Field 2”)
set PTRows to {"Row Field 1"}
set PTCols to {"Column Field 1", "Column Field 2"}
tell application "Microsoft Excel"
activate
set source_data to range "B4:E20" of sheet "Data"
set pivot_table to make new pivot table at beginning of active sheet with properties {source data:source_data}
add fields to pivot table PTable row fields PTRows column fields PTCols with add to table
end tell
That’s very cool orliotis! Thanks for posting your work.
Since I’d tried it so many different ways, I’d assumed creating pivot tables was disabled in applescript. I swear I’ve tried the syntax you’ve used, but got an error at “make new pivot table”. I’ll try your code when get back to my home computer.
I’ve got it working in my script using GUI scripting but I may go back and streamline it if I find some time. I’ll let you know if I get any further than you have but I probably won’t be able to get to it for several days. Btw, I seem to recall a command something like “drag to data field”. I’m not sure how that’s different from “add fields to pivot table”, but maybe different enough to work.
“drag to data field” was a good idea. Looks like that’s a boolean property, though. I can’t find a way to get it to actually DRAG the data to the data field slot.
But I DID figure out how to add a data field another way. It’s a little clunky, but it works. The script below:
- creates a pivot table
- adds a field for one of the column headers
- converts that field into a data field
- adds row and column fields for the pivot table
What you end up with is an honest-to-god pivot table. Nothing fancy, but it’s there. You can all the data, row, and column fields you want.
What I haven’t figured out yet is how to change the function that’s acting on the data field. That looks like it’s probably going to be pretty straightforward. Hopefully. But this is Applescript, so maybe not : )
Here’s the code that does the trick:
tell application "Microsoft Excel"
activate
set source_data to range "B4:E20" of sheet "Data"
set pivot_table to make new pivot table at beginning of active sheet with properties {source data:source_data}
add fields to pivot table pivot_table row fields {"Data Field 1"}
set pivot field orientation of pivot field "Data Field 1" of pivot_table to orient as data field
add fields to pivot table PTable row fields {"Ap Store Cd"} column fields {"Column Field 1", "Column Field 2"}
end tell
Awesome! It looks like you’re getting somewhere. I’ve yet to try it out, but this might be the final piece to your puzzle:
set the function of the pivot field of range "B6" to do average
“B6” can be any cell in the data column that you want to average
Thanks. Yeah, that looks like the right approach for changing the function of the data field. I haven’t confirmed that yet, though.
The good news is that Excel does a pretty good job of guessing the right function based on the type of data in the field, so I haven’t needed to change it yet.
I also figured out how to select Page field menu options from within AppleScript. Here’s the code:
(The first statement selects a page field menu option when you know the exact text of the menu option. The second one selects the first item in the menu without having to know its name.)
set current page of pivot field "Page Field 1" of pivot table 1 of active sheet to "NO"
set current page of pivot field "Page Field 2" of pivot table 1 of active sheet to (name of pivot item 1 of pivot field "Page Field 2" of pivot_table)
The above piece of code tries to create an empty table of 2x2, but with the line “add fields to pivot table…” in script throws an error that PTable is not defined. and after modifying it to pivot_table, facing error saying "Microsoft Excel got an error: Parameter error. (-50)"