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.

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:
   
   
   Collapse this tableExpand this table

   Field Name	Data Type
   Item	Text
   Category	Text
   Price	Number

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

   Item	Category	Price
   Nails	Hardware	5
   Shirt	Clothing	23
   Hammer	Hardware	16
   Sandwich	Food	5
   Pants	Clothing	31
   Drinks	Food	2

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: