Microsoft KB Archive/286210

= How To Extract Cell Aggregate Values From the Office 2000 PivotTable Component =

Article ID: 286210

Article Last Modified on 8/23/2005

-

APPLIES TO


 * Microsoft Office PivotTable Component 9.0

-



This article was previously published under Q286210



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



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. 

 Show HTML  



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\office\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 into 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 PivotTable.</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 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

<div class="references_section">