Using VBA to add a calculated column to a pivot table in excel

[TinaS]

Good Morning!!

I currently am using Access 2003 vba to export data to an excel pivot table via automation. The table creation works great - and I have added a column to the end of the table to calculate percentages on 2 of the rows (it looks like this):


ClosedBy (All)
Closed (All)

Count of TRRID Timely
OpenMonthYear No Yes Grand Total Percent Worked Timely
04-Apr-2011 1 1 100%
05-May-2011 194 3176 3370 94%
06-Jun-2011 147 2537 2684 95%
07-Jul-2011 307 1613 1920 84%
Grand Total 648 7327 7975 92%



The problem is, since I am creating the last column in code with the percentages, if the user tries to manipulate the data in the pivot table, the percentages go away.

I haven't worked much with pivot tables, so don't know if there is a way to have a percentage column actually calculate accordingly in the pivot table? if there is a way to do it, I imagine I can program it (just need to figure out how ) I don't think adding the column to the query in access would work, as I don't believe it exports the formulas -( I could be wrong ) so that won't work. Anyone have any ideas?

THANK YOU!!!!

 

[TinaS]

thanks again. The next thing I am researching is how to get the percentages to not add into the grand total. any suggestions on that part? from what I am seeing, that is the way Excel is designed to work, so am not sure if it's possible. I've considered the possibility of adding another column to reverse the amounts, and trying to hide that column as a work around, or, removing the grand total column completely and recalculating one - the last option seems to be more feasible.

Thoughts or reccomendations?

 

[TinaS]

I've accomplished by turning off grand totals on the rows and adding a calculated field to show that info. Looking at an alternative to the column grand totals.. thanks!

 

[SkipVought]

As an alternative, build a pivot table without using the PivotTable wizard and the PivotTable object and all the neat PivotTable interactive features, by calculating all the values row by row & column by column.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TinaS]

Maybe I am wanting too much flexibility. I wanted this column of info added to the pivot table as management wants to see the percentages, but I also wanted them to have the ability to manipulate what data they see. unfortunately, adding the percent column now gives me inaccurate totals. I'm not trying to bypass all of the pivot table features and interactive capabilities, all I wanted to do was add one simple column to represent percentages, that would move/flex/adjust when the user changed selections in the pivot table.

Again, thank you for your help. I apologize if I was a little testy yesterday - as I was incredibly frustrated with this by end of day.