I am using a pivot which connects to an external datasource. When i display field list, it shows the datasource name "Source" at the top. I am trying to loop through the fields using PivotTable.PivotFields. Its not entering the loop at all. Desired behaivior: It should enter the loop and the field names should be appended to the string.
For Each pvtField As PivotField In pvttable.PivotFields
mRetVal = mRetVal & pvtField.Name & "-" & pvtField.SourceName & "-" & pvtField.SourceCaption & ","
Next
If you want to write it in VBA, here's some help.
In VBA you generally have to separate variable declarations like this:
Dim pvtField As PivotField
For Each pvtField In pvttable.PivotFields
mRetVal = mRetVal & pvtField.Name & "-" & pvtField.SourceName & "-" & pvtField.SourceCaption & ","
Next pvtField
If your pivot table is based on an external source (OLAP), then you can also loop through the CubeFields which have some different properties:
Dim pvtCubeField as CubeField
For Each pvtCubeField In pvttable.CubeFields
mRetVal = mRetVal & pvtCubeField.Name & "-" & pvtCubeField.Value & "-" & pvtCubeField.Caption & ","
Next pvtCubeField
As CubeFields from the OLAP source and PivotFields from the pivot table are linked, you may select the PivotFields by this again:
Dim pvtCubeField as CubeField, pvtField As PivotField
For Each pvtCubeField In pvttable.CubeFields
For Each pvtField In pvtCubeField.PivotFields
mRetVal = mRetVal & pvtField.Name & "-" & pvtField.SourceName & "-" & pvtField.SourceCaption & ","
Next pvtField
Next pvtCubeField
