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:
Field Name Data Type Item Text Category Text Price Number - 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 Shirt Clothing 23 Hammer Hardware 16 Sandwich Food 5 Pants Clothing 31 Drinks Food 2 - Close Access to save the file.
- 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, visit the Microsoft Office Development support site at:
Additional query words: owc pivot table
Keywords: kbhowto kbweb kbautomation KB235542