I started out trying to rename the heading with a common name on all pivot tables across several worksheets and was having problems where most of the fields were not being renamed or not found I wasn’t sure… so I tried the following test:
foreach (Worksheet worksheet in sheets)
foreach (PivotTable pt in worksheet.PivotTables)
PivotFieldCollection fields = pt.BaseFields;
for (int i = 0; i < fields.Count; i++)
fields[i].DisplayName = "HMMM" + i;
pt.RefreshDataOnOpeningFile = true;
The result is attached, all of the fields seem to have been renamed correctly as you can see in the field list, but the display text of the fields in both the table itself and the column listings near the bottom of the screenshot is only changed on the items in the row labels, not the rest of the column headings that are in values... if I manually remove one and add it back it adds it in as the new name with sum in front of it (Sum of HMMM27 in the screenshot).
Maybe I'm not doing this correctly (I was also unable to access those other columns by name); but I need to be able to change the headings on those other items as well.
Any help would be greatly appreciated.