Binaryworld - Working with the PivotTable Component's Object Model ... [ VB -> Office ]

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.

Click here to copy the following block

Sub InitializeList(pt as PivotTable)
strProvider = "Microsoft.Jet.OLEDB.4.0"
strDBPath = "c:\program files\microsoft office\office10\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