Changing Names of Pivot datafields

[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

 

[Guest]

You can designate the column heading in VBA when you add the data field. The
AddDataField function has the following arguments:
AddDataField(Field As Object, [Caption], [Function])

In this example, I have changed the default "Sum of Hours" caption to "
Hours".

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Hours"), " Hours", xlSum

If it makes mre sense to change the caption after the pivot table has been
created, you can use something like:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Hours").Caption =
_ " Hours"

I hope this helps

Stan Shoemaker
Palo Alto, CA

 

[Dave Peterson]

One of the faux pas is to multipost to different newsgroups. If you really
think you must post to lots of groups, post one message to all groups (just
include the other groups in your header).

You have another reply at your other post in .excel

 

[mo_in_france]

Stan

Thanks for the reply, and sorry for being slow in following up.

I liked your first suggestion methodology, since I can specify the name
and the function of the pivot field, before it 'appears'. However, I'm
struggling to find this AddDataField method within my object library.
Is this specific to more recent versions of Excel, or is it something
else that I'm missing out on? (I'm using French Excel 2000)
Regards

Mohsen