How To Use the PivotTable Office Web Component with VB

This article was previously published under Q235542
This article demonstrates how to use the PivotTable Office Web Component to display information on a Visual Basic form.
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

  1. Start Visual Basic and create a new standard EXE project. Form1 is created by default.
  2. Under Project|References, add a reference to the Microsoft ActiveX Data Objects 2.1 Library and then click OK.
  3. 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.
  4. Add a PivotTable to the form.
  5. In the code window for Form1, insert the following code:
    Option ExplicitDim cnnConnection As ObjectPrivate 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 = FalseEnd SubPrivate Sub Form_Terminate()  ' Remove reference to the ADO object  Set cnnConnection = NothingEnd SubPrivate 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 IfEnd Sub					
  6. 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:

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

  8. Close Access to save the file.
  9. 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.
For more information on Office Automation, visit the Microsoft Office Development support site at:
owc pivot table

Article ID: 235542 - Last Review: 10/23/2006 18:40:34 - Revision: 3.5

Microsoft Office PivotTable Component 9.0, Microsoft Office XP Web Components, Microsoft Office 2003 Web Components

  • kbautomation kbhowto kbweb KB235542