Microsoft KB Archive/294782

= How To Extract Cell Aggregate Values from the Office PivotTable Component =

Article ID: 294782

Article Last Modified on 8/23/2005

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office 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
  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. 

 Show HTML&#xa0;&#xa0;  



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 = &quot;Provider = Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source = c:\program files\microsoft office\office10\samples\northwind.mdb&quot; Pivot.CommandText = _ &quot;SELECT Orders.ShipCountry AS Country, (1-[Discount])*[Quantity]*[Order Details].[UnitPrice] &quot; & _ &quot;AS OrderAmt, Year([OrderDate]) AS [Year], [Products].ProductName &quot; & _ &quot;FROM (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) &quot; & _ &quot;INNER JOIN Products ON [Order Details].ProductID = Products.ProductID WHERE [Order Details].ProductID<=5&quot;

Dim oView Set oView = Pivot.ActiveView

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

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

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

End Function

Function ShowHTML_OnClick showModelessDialog &quot;PivotHTML.htm&quot;, Pivot, &quot;status:no;dialogWidth:600px;dialogHeight:300px&quot; End Function





 </li> <li> Paste the following code in any text or HTML editor and save the file as Pivothtml.htm in the same folder as Pivot.htm.  <STYLE> BODY {font-family:&quot;Arial&quot;} TD {font-size:&quot;10pt&quot;} </STYLE>  </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 = &quot; &quot;

document.body.innerHTML = sHTML End Function

Function Format(CellValue) If IsNull(CellValue) Then Format = &quot;&#xa0;&quot; Else Format = FormatCurrency(CellValue,0) End If End Function </SCRIPT> </HTML> </li> <li>Start Microsoft Internet Explorer and browse to Pivot.htm.</li> <li>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.</li></ol>

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

<div class="references_section">