Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Names of Pivot datafields
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Names of Pivot datafields
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").AddDataFiel d 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 "mo_in_france" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Names of Pivot datafields
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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Names of Pivot datafields
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of unique datafields in a column | Excel Discussion (Misc queries) | |||
Changing field names without affecting pivot table | Excel Discussion (Misc queries) | |||
Pivot Table DataFields are appended with a '2', why? | Excel Discussion (Misc queries) | |||
Changing range names in VBA | Excel Programming | |||
changing 'names' | Excel Programming |