How To Extract Cell Aggregate Values From the Office 2000 PivotTable Component (286210)

MORE INFORMATION

The following sample demonstrates how you can extract cell aggregate values from an Office 2000 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.

Sample

Paste the following code into any text 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.

<HTML>

<BODY>
<BUTTON ID="ShowHTML">Show HTML</BUTTON>
<P/>
<OBJECT CLASSID=clsid:0002E520-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\office\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>

Paste the following code into 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.ColumnMembers.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.ColumnMembers.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.RowMembers.TotalMember, oCol).Aggregates(0).Value
        sHTML = sHTML & "<TD>" & Format(CellValue) & "</TD>"
    Next
    
    'Retrieve the PivotTable Grand Total.
    CellValue = oData.Cells(oData.RowMembers.TotalMember, oData.ColumnMembers.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>

Start Microsoft Internet Explorer and browse to Pivot.htm.
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 PivotTable.

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 to 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).

Office XP PivotTable Component

For additional information on extracting cell aggregate values (and HTML) with the Microsoft Office XP PivotTable Component, click the article number below to view the article in the Microsoft Knowledge Base:

294782 How To Extract Cell Aggregate Values From the Office XP PivotTable Component

How To Extract Cell Aggregate Values From the Office 2000 PivotTable Component (286210)

SUMMARY

MORE INFORMATION

Sample

REFERENCES