How To Extract Cell Aggregate Values from the Office PivotTable Component (294782)


The information in this article applies to:

  • Microsoft Office XP Web Components
  • Microsoft Office 2003 Web Components
This article was previously published under Q294782

SUMMARY

This article demonstrates how you can programmatically retrieve cell values from the Office PivotTable component.

MORE INFORMATION

The following sample demonstrates how you can extract cell aggregate values from an Office PivotTable. The code also extracts row and column member captions from the PivotTable so that all the data can be combined and nicely displayed in an HTML table.

NOTE: The Office PivotTable component has a new property, HTMLData, that you can use to extract an HTML representation of the PivotTable's view and data. The sample below specifically illustrates the extraction of various values from cells in the PivotTable and coincidentally displays the sample in HTML. If your goal is to retrieve an HTML representation of the PivotTable, you might want to consider using the HTMLData property instead of iterating PivotTable elements to build your own HTML. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

294798 How To Use ASP to Create an Office XP PivotTable and Display the Results as HTML

Sample to Extract Cell Aggregate Values from a PivotTable

  1. Paste the following code in any text editor or HTML editor and save the file as Pivot.htm.

    Note You may need to modify the path to the sample Northwind database in the code so that it matches the path for your installation of Microsoft Office.

    Note The following code uses the Office XP version of the PivotTable object. For the Office 2003 version of the PivotTable object, replace the value clsid:0002E552-0000-0000-C000-000000000046 in the code with clsid:0002E55A-0000-0000-C000-000000000046. 
    <HTML>

<BODY>
<BUTTON ID="ShowHTML">Show HTML</BUTTON>&#xa0;&#xa0;
<P/>
<OBJECT CLASSID=clsid:0002E552-0000-0000-C000-000000000046 id="Pivot" VIEWASTEXT></OBJECT>

<SCRIPT Language = 'VBScript'>

Function Window_OnLoad()   'Build the Pivot Table

    Pivot.AllowDetails = False
    Pivot.AllowPropertyToolbox = False
    Pivot.DisplayToolbar = False
    
    'Connect to the database and provide the commandtext for the rowset.
    Pivot.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source = c:\program files\microsoft office\office10\samples\northwind.mdb"
        
    Pivot.CommandText = _
        "SELECT Orders.ShipCountry AS Country, (1-[Discount])*[Quantity]*[Order Details].[UnitPrice] " & _
        "AS OrderAmt, Year([OrderDate]) AS [Year], [Products].ProductName " & _
        "FROM (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) " & _
        "INNER JOIN Products ON [Order Details].ProductID = Products.ProductID WHERE [Order Details].ProductID<=5"

    Dim oView
    Set oView = Pivot.ActiveView

    'Add fields to the row axis and column axis for grouping.
    oView.RowAxis.InsertFieldSet oView.Fieldsets("Country")
    oView.RowAxis.InsertFieldSet oView.Fieldsets("ProductName")
    oView.ColumnAxis.InsertFieldSet oView.Fieldsets("Year")

    'Add a total for the OrderAmt fieldset.
    oView.DataAxis.InsertTotal oView.AddTotal("SalesTotal", _
           oView.Fieldsets("OrderAmt").Fields(0), Pivot.Constants.plFunctionSum)
    oView.Totals("SalesTotal").NumberFormat = "$#,##0"
	
    'Collapse rows and columns.
    oView.Fieldsets("Year").Fields(0).Expanded = False
    oView.Fieldsets("ProductName").Fields(0).Expanded = False

    'Hide the Filter axis.
    oView.FilterAxis.Label.Visible = False

End Function

Function ShowHTML_OnClick()
      
    showModelessDialog "PivotHTML.htm", Pivot, "status:no;dialogWidth:600px;dialogHeight:300px"
  
End Function

</SCRIPT>

</BODY>

</HTML>
  2. Paste the following code in any text or HTML editor and save the file as Pivothtml.htm in the same folder as Pivot.htm.
    <HTML>
<STYLE>
BODY {font-family:"Arial"}
TD {font-size:"10pt"}
</STYLE>
<BODY>
</BODY>

<SCRIPT Language = 'VBScript'>

Function Window_OnLoad()

    Dim oPivot
    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 nCols
    Dim sHTML
    Dim CellValue
    Dim c, r
    
    Set oData = oPivot.ActiveData
    nCols = oData.ColumnMembers.Count
    
    'This is the opening table tag.
    sHTML = "<TABLE BORDER='1' WIDTH='100%' BORDERCOLOR='Gray' CELLSPACING=0>" & vbCrLf & _
            "<COLGROUP SPAN='2' BGCOLOR='Silver'></COLGROUP>" & vbCrLf & _
            "<COLGROUP SPAN='" & nCols + 1 & "' ALIGN='Right'></COLGROUP>"
        
    'Retrieve column member captions.
    sHTML = sHTML & vbCrLf & "  <TR><TD COLSPAN='2'>&#xa0;</TD>"
    For Each oCol In oData.ColumnMembers
        sHTML = sHTML & "<TD BGCOLOR='Silver'>" & oCol.Caption & "</TD>"
    Next
    sHTML = sHTML & "<TD BGCOLOR='Silver'>Grand Total</TD></TR>"
    
    'Iterate both levels of row headings to retrieve row member captions and aggregates.
    For Each oRow0 In oData.RowMembers
    
        For r = 0 To oRow0.ChildMembers.Count - 1
            Set oRow1 = oRow0.ChildMembers(r)
            'Retrieve Row Member Captions
            If r = 0 Then
                sHTML = sHTML & vbCrLf & _
                        "  <TR><TD ROWSPAN='" & oRow0.ChildMembers.Count + 1 & "'>" & _
                        oRow0.Caption & "</TD><TD>" & oRow1.Caption & "</TD>"
            Else
                sHTML = sHTML & vbCrLf & "  <TR><TD>" & oRow1.Caption & "</TD>"
            End If
            'Retrieve cell aggregates for this row and each column.
            For c = 0 To nCols - 1
                CellValue = oData.Cells(oRow1, oData.ColumnMembers(c)).Aggregates(0).Value
                sHTML = sHTML & "<TD>" & Format(CellValue) & "</TD>"
            Next
            'Retrieve the Grand Total for this second level row.
            CellValue = oData.Cells(oRow1, oData.ColumnAxis.ColumnMember.TotalMember).Aggregates(0).Value
            sHTML = sHTML & "<TD>" & Format(CellValue) & "</TD></TR>"
        Next
        
        'Retrieve the totals for this first level row.
        sHTML = sHTML & vbCrLf & "  <TR><TD>Total</TD>"
        For Each oCol In oData.ColumnMembers
            CellValue = oData.Cells(oRow0, oCol).Aggregates(0).Value
            sHTML = sHTML & "<TD>" & Format(CellValue) & "</TD>"
        Next
        CellValue = oData.Cells(oRow0, oData.ColumnAxis.ColumnMember.TotalMember).Aggregates(0).Value
        sHTML = sHTML & "<TD>" & Format(CellValue) & "</TD></TR>"
        
    Next
    
    'Retrieve Grand Totals for each column.
    sHTML = sHTML & vbCrLf & "  <TR><TD COLSPAN='2'>Grand Total</TD>"
    For Each oCol In oData.ColumnMembers
        CellValue = oData.Cells(oData.RowAxis.RowMember.TotalMember, oCol).Aggregates(0).Value
        sHTML = sHTML & "<TD>" & Format(CellValue) & "</TD>"
    Next
    
    'Retrieve the PivotTable Grand Total.
    CellValue = oData.Cells(oData.RowAxis.RowMember.TotalMember, oData.ColumnAxis.ColumnMember.TotalMember).Aggregates(0).Value
    sHTML = sHTML & "<TD>" & Format(CellValue) & "</TD></TR>"
    
    'This is the table closing tag.
    sHTML = sHTML & vbCrLf & "</TABLE>"

    document.body.innerHTML = sHTML
    
End Function

Function Format(CellValue)
  If IsNull(CellValue) Then
    Format = "&#xa0;"
  Else
    Format = FormatCurrency(CellValue,0)
  End If
End Function
    
</SCRIPT>
</HTML>
  3. Start Microsoft Internet Explorer and browse to Pivot.htm.
  4. After the Web page is rendered, click ShowHTML. A Web dialog box appears with an HTML table that contains the row and column captions and the cell aggregates in the Pivot Table.
Additional Notes

If you attempt to adapt this code to your PivotTable, you should consider the following:
  • The sample code extracts values only from aggregate cells. Therefore, if you use this code against a PivotTable with row or column members that are expanded to show detail cells, values in the detail cells are not extracted and are not portrayed in the resulting HTML.
  • The sample code is written so that the number of rows and columns in the PivotTable hierarchy is known (the sample expects two levels on the row axis and one level on the column axis). Therefore, you need consider the row and column members in your PivotTable's member hierarchy when you modify this code.
  • The sample code extracts this information from the PivotTable and displays it in HTML. You can modify the code to display the data in a format of your choice (for example, XML, tab-delimited text, or comma-delimited text).

REFERENCES

For more information, see the following Microsoft Web sites:

Microsoft Office Web Components
http://support.microsoft.com/ofd

Microsoft Office Developer Center
http://msdn.microsoft.com/office

For information on extracting aggregate information with the PivotTable component from Visual Basic, see the following Microsoft Knowledge Base article:

235542 How To Use the PivotTable Office Web Component with VB

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbhowto kbOfficeWebPivot kbPivotTable KB294782 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.