Microsoft KB Archive/235542

= How To Use the PivotTable Office Web Component with VB =

Article ID: 235542

Article Last Modified on 10/23/2006

-

APPLIES TO


 * Microsoft Office PivotTable Component 9.0
 * Microsoft Office Web Components
 * Microsoft Office Web Components

-



This article was previously published under Q235542



SUMMARY
This article demonstrates how to use the PivotTable Office Web Component to display information on a Visual Basic form.



MORE INFORMATION
The Office Web Components are a set of controls that enable browsing of Office data. Because the Web Components are controls, you can place them on a Visual Basic form and automate the components to display data.

Building the Automation Sample
 Start Visual Basic and create a new standard EXE project. Form1 is created by default. Under Project|References, add a reference to the Microsoft ActiveX Data Objects 2.1 Library and then click OK. Press the CTRL+T keys to display the Add Components dialog box. Add the Microsoft Office Web Components, and then click OK.

NOTE: Office XP includes both Office 2000 and Office XP Web Components. To use the Office 2003 Web Components, add the Microsoft Office Web Components 11.0 library. To use the Office 2000 Web Components, add the Microsoft Office Web Components 9.0 library. To use the Office XP Web Components, add the Microsoft Office Web Components 10.0 library. Add a PivotTable to the form.  In the code window for Form1, insert the following code: Option Explicit

Dim cnnConnection As Object

Private Sub Form_Load

Dim strProvider As String Dim view As PivotView Dim fsets As PivotFieldSets Dim c As Object Dim newtotal As PivotTotal strProvider = "Microsoft.Jet.OLEDB.4.0" ' Create an ADO object Set cnnConnection = CreateObject("ADODB.Connection") ' Set the provider and open the connection to the database cnnConnection.Provider = strProvider cnnConnection.Open "C:\pivottest.mdb" ' Set the pivot table's connection string to the cnnConnection's connection string PivotTable1.ConnectionString = cnnConnection.ConnectionString ' SQL statement to get everything from table1 PivotTable1.CommandText = "Select * from table1" ' Get variables from the pivot table Set view = PivotTable1.ActiveView Set fsets = PivotTable1.ActiveView.FieldSets Set c = PivotTable1.Constants ' Add Category to the Row axis and Item to the Column axis view.RowAxis.InsertFieldSet fsets("Category") view.ColumnAxis.InsertFieldSet fsets("Item") ' Add a new total - Sum of Price Set newtotal = view.AddTotal("Sum of Price", view.FieldSets("Price").Fields(0), c.plFunctionSum) view.DataAxis.InsertTotal newtotal view.DataAxis.InsertFieldSet view.FieldSets("Price") ' Set some visual properties PivotTable1.DisplayExpandIndicator = False PivotTable1.DisplayFieldList = False PivotTable1.AllowDetails = False End Sub

Private Sub Form_Terminate ' Remove reference to the ADO object Set cnnConnection = Nothing End Sub

Private Sub PivotTable1_DblClick Dim sel As Object Dim pivotagg As PivotAggregate Dim sTotal As String Dim sColName As String Dim sRowName As String

Dim sMsg As String ' Get the selection object you double-clicked on  Set sel = PivotTable1.Selection ' If it is a aggregate, you can find information about it  If TypeName(sel) = "PivotAggregates" Then ' Select the first item

Set pivotagg = sel.Item(0) ' Display the value MsgBox "The cell you double-clicked has a value of '" & pivotagg.Value & "'.", vbInformation, "Value of Cell" ' Get variables from the cell sTotal = pivotagg.Total.Caption sColName = pivotagg.Cell.ColumnMember.Caption sRowName = pivotagg.Cell.RowMember.Caption ' Display the row and column name sMsg = "The value is " & sTotal & " by " & sRowName & " by " & sColName MsgBox sMsg, vbInformation, "Value Info" End If End Sub  Next, create a sample database using Microsoft Access. Start Access and choose Blank Access Database. Save the file as c:\pivottest.mdb. Double-click Create Table in Design View. Enter the following data:

 Close the Window and save changes as Table1. Select No for creating a key. Double-click Table1 and enter the following data:

</li> Close Access to save the file.</li> Press the F5 key to run the project. After running, the pivot table should populate. Double-clicking on a cell with data shows the value of the cell and the cell column and row names.</li></ol>

<div class="references_section">