First, I would like to set the caption for a pivot table in a VBScript macro. I have this piece of code so far
sub cleanUp()
set sheet = ActiveDocument.Sheets("Summary")
set summaryTable = sheet.CreatePivotTable
summaryTable.addDimension "AccountGroupDesc"
summaryTable.addDimension "AccountDesc"
summaryTable.addExpression "Sum({<[Fiscal Year]={$(vCurrentYear)}>}ExpenseActual)/1000"
set props = summaryTable.getProperties
set expr = props.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.Label.v = "Expenses $(vCurrentYear)"
expr.NumAdjust = 1 'center
expr.LabelAdjust = 1 'center
summaryTable.SetProperties props
set sp = sheet.GetProperties
sp.ZoomFactor = 1
sheet.SetProperties sp
set capt = summaryTable.GetCaption.Name.v
'set v = ActiveDocument.Variables("vVarCapt")
'v.SetContent "Sales",true
end sub
The line set capt = summaryTable.GetCaption.Name.v bears some error in it. The last two lines seem not to make it. I would like to set the caption/title to string "Sales" for instance.
Second, I would like to autofit the width of columns for dimensions to the header's text. Is there a way ? Alternatively, how to adjust the column width to text in all the cells ?
