Microsoft Office 2000/Visual Basic Programmer's Guide

Working with the PivotTable Component's Object Model

The PivotTable List control is represented in the object model by the PivotTable object. You use the DataSource property to specify the source of the data to be displayed in the control. Typically this property setting will consist of an ADO data source or another one of the Office Web Component controls.

The PivotTable object has two child objects: the PivotData object and the PivotView object. The PivotData object represents the data in a PivotTable List control, and the PivotView object represents a specific view of the data in the PivotTable List control. In the following example, the ActiveView property is used to return the PivotView object so that various properties of the object can be specified.

The following code sample illustrates how to create a simple list by using the PivotTable List control. The list is filled with customer data from an ADO data source, in this case, the Northwind sample database.

Sub InitializeList(pt)
   strProvider = "Microsoft.Jet.OLEDB.4.0"
   strDBPath = "c:\program files\microsoft office\office\samples\Northwind.mdb"

   strSQL = "SELECT * FROM Customers"
   strTitle = "Customers of the Northwind Company"

   Set cnnConnection = CreateObject("ADODB.Connection")
   cnnConnection.Provider = strProvider
   cnnConnection.Open strDBPath

   ' Set the connection string.
   pt.ConnectionString = cnnConnection.ConnectionString
   pt.CommandText = strSQL
   
   set view = pt.ActiveView

   ' Automatically add all result columns to the detail area.
   view.AutoLayout
   
   ' Set the title.
   view.Titlebar.Caption = strTitle

   ' Hide the drop areas.
   view.RowAxis.Label.Visible = False
   view.ColumnAxis.Label.Visible = False

   ' Set detail maximum width so that it doesn't have detail scroll bars.
   view.DetailMaxHeight = 32000
   view.DetailMaxWidth = 32000
   
   ' Turn off AutoFit.
   pt.AutoFit = False
   pt.Width = "100%"
   pt.Height = "65%"

   ' Disallow grouping.
   pt.AllowGrouping = False
End Sub 

The InitializeList procedure is available in the PivotListSimpleListExample.htm file in the ODETools\V9\Samples\OPG\Samples\CH12 subfolder on the Office 2000 Developer CD-ROM. This same view of the Northwind customer data could have easily been created in a data access page by simply dragging the Customers table from the field list to the page in Design view.

For complete documentation of the PivotTable List control's object model, see the Msowcvba.chm Help file, which is located in the C:\Program Files\Microsoft Office
\Office\1033 subfolder.

Note   The path to the Msowcvba.chm Help file reflects the language ID folder (1033) for U.S. English language support in Office. The language ID folder below C:\Program Files\Microsoft Office\Office differs for each language.