I am having a hard time finding a detailed object model for the pivottable component with a few examples. Has anyone worked with the activedata portion.
I am trying to enumerate through the record set to write the details to a .csv.
I think I am close but the problem is I can't find key words and Im guessing.
The following code goes through 1 aggregate only. I am currently looking for the nomenclature that will return the number of aggregates from the data area of the pivot table. The following is html for quick and dirty extract which works:
Set oPivot = dialogArguments
Dim oData 'As PivotData
Dim oRow0 'As PivotMember -- First level Row Member
Dim oRow1 'As PivotMember -- Second Level Row Member
Dim oCol 'As PivotMember -- First level Column Member
Dim c, r
Set oData = oPivot.ActiveData
nCols = oData.ColumnMembers.Count
sHTML = "<TABLE BORDER='1' WIDTH='100%' BORDERCOLOR='Gray' CELLSPACING=0>" & vbCrLf & _
"<COLGROUP SPAN='1' BGCOLOR='Silver'></COLGROUP>" & vbCrLf & _
"<COLGROUP SPAN='" & nCols + 1 & "' ALIGN='Right'></COLGROUP>"
'Retrieve Column Member Captions.
sHTML = sHTML & vbCrLf & " <TR><TD COLSPAN='1'> </TD>"
For Each oCol In oData.ColumnMembers
sHTML = sHTML & "<TD BGCOLOR='Silver'>" & Trim(oCol.Caption) & "</TD>"
sHTML = sHTML & vbCrLf & " </TR>"
'Retrieve Row Member Captions and Data Sets
For Each oRow0 In oData.RowMembers
sHTML = sHTML &"<tr><td>"&Trim(oRow0.Caption)&"</td>"
'msgbox Trim(oData.Cells(oRow0,oData.ColumnMembers(0)).Agg regates(0).Value)
'looping through data cells
For c = 0 To nCols - 1
sHTML = sHTML & "<TD>" & oData.Cells(oRow0, oData.ColumnMembers(c)).Aggregates(0).Value & "</TD>"
sHTML = sHTML &"</tr>"
'This is the table closing tag.
sHTML = sHTML & vbCrLf & "</TABLE>"