adjusting pivot table field names to language in use

[schmidverena]

hi,

i'm currently working on a pivot table using a german version of Excel
2003.
Whenever I open the file using the english verison the pivot field
names are stilled displayed in german. ("Summe von... " instead of
"Sum of...")

but the really weird stuff starts now:
if I change the aggregation function from sum to count, the values
displayed in the table change accordingly but the pivot table field
name remains at "Summe von..." (in german!!)

seems like an annoying bug :-(
any ideas how to change/update the names displayed programmatically?

thanx for any suggestions

Verena

 

[Guest]

You can change the field names manually by clicking the cell with the field
heading you want to change, then edit it in the Formula Bar.

To do the same using VBA, you change the Caption property of the field, as
follows:

Sub AAAA()
'Change Product to ProductX
ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Product").Caption = "ProductX"
'Change Sum of Quantity to Count of Quantity
ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Sum of Quantity").Caption = _
"Count of Quantity"
End Sub

Hope this helps,

Hutch

 

[schmidverena]

Hi Hutch,

thanks for your answer!
I already knew about this function. Unfortunately I won't be able to
apply it like that, as the label should correspond with the actual
aggregation function chosen.
Unless there would be a way to retrieve information concerning the
aggregation function chosen (sum/min/max/count/etc..) ?

thx
Verena

 

[Guest]

If you are selecting the aggregation function programmatically, then you
already know which one it is. The property you want is the Function property
of the PivotField object. You can read or write this property, as follows:

Sub AAAA()
MsgBox ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Sum of Quantity").Function
'In Excel 2003:
' Sum returns -4157 (xlSum constant)
' Count returns -4112 (xlCount constant)
' Average reutns -4106 (xlAverage constant)
ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Sum of Quantity").Function _
= xlCount
End Sub

Regards,

Hutch