VBA, Changing Datafield Name

[mo_in_france]

Hi ALL

This is my first post, so excuse me for any faux pas on etiquette.

I'm creating various pivot tables by VBA code. For each datafield I
change the name so that the columns would not be too wide, and so that
I don't get the "Sum of " thing.

My methodolgy of changing the name relies on the datafield starting
with 'Somme' (Note that I use French excel). I notice that Excel 02 has
'Somme de ' meaning that my code comes to a halt. I have stuck in a few
lines of 'IF' statements relating to Excel version to remedy this.
However, this is not all encompassing, and doesn't help when I pass
some code to my colleagues not using French excel.

Does there exist a method by whereby I can return the datafield's name
irrelevant of which Excel version (French or otherwise), and based upon
the name of the field being summed (or counted for that matter).
Regards

 

[Dave Peterson]

Instead of fixing the problem after the fact, how about trying to make the
problem not occur.

I recorded a macro that created a simple pivottable in xl2002.

I could change the title of each field while I was creating that field.

Option Explicit

Sub testme()

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R27C7").CreatePivotTable _
TableDestination:="", TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

ActiveSheet.PivotTables("PivotTable3").AddFields _
RowFields:=Array("$A$1"), ColumnFields:=Array("Data")

With ActiveSheet.PivotTables("PivotTable3").PivotFields("c")
.Orientation = xlDataField
.Position = 1
.Value = "My title for A"
.Function = xlSum
End With

With ActiveSheet.PivotTables("PivotTable3").PivotFields("b")
.Orientation = xlDataField
.Position = 2
.Value = "my title for B"
.Function = xlCount
End With

End Sub

 

[mo_in_france]

Dave

thanks for the response, and the tip on double posting.

I adjusted my code to create my datafields using your method above. My
goal had been to specify the name and functions before they 'appear',
and that's just what your suggestion did!

Thanks
Mohsen