HOWTO: Use the PivotTable Office Web Component with VB

• Microsoft Office 2000
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

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

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 9.0 and click OK.



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

   Item	Category	Price
   Nails	Hardware	5.25
   Shirt	Clothing	23.00
   Hammer	Hardware	16.25
   Sandwich	Food	5.00
   Pants	Clothing	31.00
   Drinks	Food	2.25




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.

REFERENCES

For more information on Office Automation, please visit the Microsoft Office Development support site at:

http://support.microsoft.com/support/officedev/

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Mark Durrett, Microsoft Corporation

Additional query words: owc pivot table

Keywords : kbweb kbAutomation kbVBp500 kbVBp600 kbGrpDSO kbOffice2000 kbDSupport
Version : WINDOWS:2000,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto