Dynamically building Pivot Table with VBA

[FergalK]

Hi
I am currently building an Excel VBA reporting project .
One requirement is for there to be a Pivot Table, which can be built dynamically/change it's fields depending on the source data.
It's all going well so far, but I have a problem as follows:

My Pivot table variables are as follows:

• PivotDataFieldName
• PivotDataFieldCaption (value is the same as the Filed name - ie; unchanged)
• PivotDataFieldAggregation (eg; value is xlCount)

This code works fine:
ActiveSheet.PivotTables("ReportAnalytics_1").adddatafield ActiveSheet.PivotTables("ReportAnalytics_1").PivotFields(PivotDataFieldName), "Count of Stocking ID", xlCount

This code does not work:
ActiveSheet.PivotTables("ReportAnalytics_1").adddatafield ActiveSheet.PivotTables("ReportAnalytics_1").PivotFields(PivotDataFieldName), PivotDataFieldCaption, xlCount

This code does not work:
ActiveSheet.PivotTables("ReportAnalytics_1").adddatafield ActiveSheet.PivotTables("ReportAnalytics_1").PivotFields(PivotDataFieldName), "Count of Stocking ID",PivotDataFieldAggregation

Any idea why it is happy enough using a variable for the Field name, but not for either the Caption or the Aggregation type?

Thanks a lot!

Fergal

 

[par60056]

When you add a data field it becomes another item. It cannot have the same name as the original field. That is why when you add something it has a name "Sum of xxx" or "Count of XXX" because xxx is already taken.

In the data, I tend to make fields that I know will be data fields abbreviations so that I can use the correct name on the data field. So in the data I will have something like "tVal" as the column header so I can use "Value" in the pivot for "Sum of tVal"

 

[FergalK]

When you add a data field it becomes another item. It cannot have the same name as the original field. That is why when you add something it has a name "Sum of xxx" or "Count of XXX" because xxx is already taken.

cool - thanks a lot for that pointer, so now the following line works fine:
ActiveSheet.PivotTables("ReportAnalytics_1").adddatafield ActiveSheet.PivotTables("ReportAnalytics_1").PivotFields(PivotDataFieldName), "Count of" & PivotDataFieldCaption, xlCount

but still having an issue when dynamically assigning the Aggregation type (eg; xlCount):

ActiveSheet.PivotTables("ReportAnalytics_1").adddatafield ActiveSheet.PivotTables("ReportAnalytics_1").PivotFields(PivotDataFieldName), "Count of" & PivotDataFieldCaption, PivotDataFieldAggregation

any ideas on that bit?
Thanks again - this really helps

 

[par60056]

I have never tried something like that.

What type is PivotDataFieldAggregation? I have never tried to set the dataField function that way. I generally know what it should be. I think if I were taking the value from input I would use a CASE statement to add the field with the right function.

What version of Excel are you using?

 

[FergalK]

Using Excel 2010
The type of PivotDataFieldAggregation is String, I also tried Variant but neither worked

 

[par60056]

try integer. value of it should be -4112

Look at XLXonsolidationFunction in the developer help.

 

[FergalK]

Wow! That worked!
Excellent
Thanks a million - I think I never would have discovered that solution - that's extremely useful

 

[par60056]

Thanks for the feedback. glad it helped.