How To Use the PivotTable Office Web Component with VB


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

  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 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
  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:

ID d'article : 235542 - Dernière mise à jour : 23 mars 2009 - Révision : 1