My pivot table has abbreviated pivot table field names.
Is it possible to somehow customise these?
For example my pivot table has a field called “name” that I want to customised/rename to “Device name”
I saw an old question that was closed about a year ago here where no answer was found:
You can change the headers of the row fields and column fields. Just select the corresponding header cells (using the arrows) and change.
You cannot change the headers of the data fields in this way.
I don’t want to change the field that is used, I just want to relabel it in the pivot table output itself.
My workaround is to rename the field headers of the source data range, but this means that every time I import data I need to adjust all the source data field headers again.
I am pretty certain that Excel allowed the pivot table to add custom label names as a formatting type of feature, which remained in between source data range updates.
Cross posted macro solution from Text substitutions in pivot table headings with a call for testing.
Copy of same Basic module here:
REM ***** BASIC *****
Sub Main
Dim a()
pv = getPilotAtActiveCell()
fp = pv.getPageFields()
if fp.getCount() then bas_pushArray(a(), "Page Field")
fc = pv.getColumnFields()
if fc.getCount() then bas_pushArray(a(), "Column Field")
fr = pv.getRowFields()
if fr.getCount() then bas_pushArray(a(), "Row Field")
fd = pv.getDataFields()
if fd.getCount() then bas_pushArray(a(), "Data Field")
dlg = getListboxDialog("Select Field Type", "I want to rename a ...", a())
dlg.setPosSize(200,150,0,0,3)
if dlg.execute()=0 then exit sub
sType = dlg.getControl("ListBox").getSelectedItem()
if sType = "Page Field" then
flds = fp
elseif sType = "Column Field" then
flds = fc
elseif sType = "Row Field" then
flds = fr
elseif sType = "Data Field" then
flds = fd
else
exit sub
endif
REM getElementNames does not return the displayed names
REM this is how we get them:
Redim a()
for i = 0 to flds.getCount()-1
fld = flds.getByIndex(i)
bas_pushArray(a(), fld.getName())
next
dlg = getListboxDialog("Select Field Name", "I want to rename the field named ...", a())
dlg.setPosSize(200,150,0,0,3)
if dlg.execute() = 0 then exit sub
s = dlg.getControl("ListBox").getSelectedItem()
REM get by position because they may have equal names
i = dlg.getControl("ListBox").getSelectedItemPos()
fld = flds.getByIndex(i)
' mri fld
' exit sub
sNew = inputbox("Enter new name for " & sType &" "&s ,"New Field Name", s, 200, 150)
if len(sNew)= 0 then exit sub
fld.setName(sNew)
End Sub
Function getPilotAtActiveCell()
'calls: getActiveCell
oCtrl = ThisComponent.getCurrentController()
oCell = getActiveCell(oCtrl)
oDPE = oCell.Spreadsheet.DataPilotTables.createEnumeration()
do while oDPE.hasMoreElements()
oDP = oDPE.nextElement()
oAddr = oDP.getOutputRange()
n = oCell.queryIntersection(oAddr).getCount()
if n > 0 then
getPilotAtActiveCell = oDP
else
getPilotAtActiveCell = Null
endif
loop
End Function
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
as1() = Split(oView.ViewData, ";")
lSheet = CLng(as1(1))
sDum = as1(lSheet +3)
as1() = Split(sDum, "/")
on error goto errSlash
lCol = CLng(as1(0))
lRow = CLng(as1(1))
on error goto 0
getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
if NOT(bErr) then
bErr = True
as1() = Split(sDum, "+")
resume
endif
End Function
Function getListboxDialog(sTitle$,sFixedText$,aListItems())
'calls: addAwtModel, stackVertically
Dim oDM,oDlg,oTools,oRegion,oRect,oPoint
oDM = CreateUnoService("com.sun.star.awt.UnoControlDialogModel")
oDM.Title = sTitle
REM addAwtModel dialogModel, c.s.s.awt.UnoControl<type>, name of control,
REM (propertyNames), (propertyValues) propertyNames in alpabetical order!
addAwtModel oDM,"FixedText","FixedText", _
Array("Label","MultiLine"), _
Array(sFixedText,True)
addAwtModel oDM,"ListBox","ListBox", _
Array("Dropdown","StringItemList"), _
Array(True,aListItems())
addAwtModel oDM,"Button","btnOK", _
Array("DefaultButton","Label","PushButtonType"), _
Array(True,"OK",com.sun.star.awt.PushButtonType.OK)
addAwtModel oDM,"Button","btnCancel", _
Array("Label","PushButtonType"), _
Array("Cancel",com.sun.star.awt.PushButtonType.CANCEL)
oDlg = CreateUnoService("com.sun.star.awt.UnoControlDialog")
oDlg.setModel(oDM)
oDlg.setVisible(True)
oTools = oDlg.getPeer.getToolkit
oRegion = oTools.createRegion
oPoint = createUnoStruct("com.sun.star.awt.Point")
oPoint.X = 5
oPoint.Y = 5
oRect = stackVertically(oDlg,Array("FixedText","ListBox","btnOK","btnCancel"),oRegion,oPoint,5)
oDlg.setPosSize(0,0, oRect.Width +oRect.X, oRect.Height +oRect.Y,com.sun.star.awt.PosSize.SIZE)
getListboxDialog = oDlg
End Function
Sub addAwtModel(oDM,srv,sName,aNames(),aValues())
Dim oCM
oCM = oDM.createInstance("com.sun.star.awt.UnoControl"+ srv +"Model")
oCM.setPropertyValues(aNames(),aValues())
oDM.insertByName(sName,oCM)
End Sub
Function getControlSize(oCtrl)
'''Return preferred width and/or height, if not already set larger.'''
Dim curPS, prefSz
curPS = oCtrl.getPosSize()
prefSz = oCtrl.getPreferredSize()
if curPS.Width >= prefSz.Width then prefSz.Width = curPS.Width
if curPS.Height >= prefSz.Height then prefSz.Height = curPS.Height
getControlSize = prefSz
End Function
Function stackVertically(oDlg,sNames(),oRegion,oPoint,optional spc)
'calls: getControlSize
'Stack list of controls vertically, starting at point with optional spaces below.
'Calculate and set preferred width and/or height if not already set >0.
'Out: resized oRegion with added rectangles.
'Returns new bounds of region'''
Dim y&, i%, s$, c, sz
if isMissing(spc) then spc = 0
y = oPoint.Y
for i = 0 to uBound(sNames())
s = sNames(i)
c = oDlg.getControl(s)
sz = getControlSize(c)
c.setPosSize(oPoint.X, y, sz.Width, sz.Height, com.sun.star.awt.PosSize.POSSIZE)
oRegion.unionRectangle(c.getPosSize())
y = y +sz.Height +spc
next
stackVertically = oRegion.getBounds()
End Function
Sub bas_PushArray(xArray(),vNextElement)
Dim iUB%,iLB%
iLB = lBound(xArray())
iUB = uBound(xArray())
If iLB > iUB then
iUB = iLB
redim xArray(iLB To iUB)
else
iUB = iUB +1
redim preserve xArray(iLB To iUB)
endif
xArray(iUB) = vNextElement
End SubDoes it exist?
If I would know the interactive way, I would not waste my time with silly Basic code.