PivotTable Objects

 < Day Day Up > 



Each Excel worksheet can hold multiple PivotTables. To access a particular PivotTable object, you start with the Worksheet object that represents the worksheet containing the PivotTable that you want to manipulate. Then you use the PivotTables collection to reach the specific PivotTable object that you want to manipulate.

Each PivotTable object contains several key objects that represent the various pieces of a PivotTable. (See Figure 16-6.)

click to expand
Figure 16-6: The parts of a PivotTable are represented by a collection of different types of objects.

PivotTables Collection

The PivotTables collection contains the set of all PivotTables in a particular worksheet. Table 16-1 lists the key properties and methods for the PivotTables collection. You can access the PivotTables property from a Worksheet object using the following code:

ActiveSheet.PivotTables(1)
Table 16-1: Key Properties and Methods of the PivotTables Collection

Property/Method

Description

Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion)

Method: adds a new PivotTable to the collection. PivotCache refers to the data that will be displayed in the PivotTable; TableDestination passes a Range object whose upper-left corner marks the location of the PivotTable in the worksheet; TableName contains the name of the PivotTable (optional); ReadData, when True, caches all records from an external database in PivotCache; DefaultVersion specifies the version of Excel that created the PivotTable.

Count

Property (read-only): returns the number of items in the PivotTables collection.

Item(index)

Method: returns the PivotTable object reference associated with index. Index may refer to the PivotTable by either its name or its relative position in the collection.

PivotTable Object

The PivotTable object represents a single PivotTable. You can use this object to access an existing PivotTable on a worksheet, or you can use the Add method from the PivotTables collection to add a new one. You can even use the PivotTableWizard method from the Worksheet object to create a new PivotTable.

Table 16-2 contains a list of the key properties and methods associated with the PivotTable object.

Table 16-2: Key Properties and Methods of the PivotTable Object

Property/Method

Description

AddDataField(Field, Caption, Function)

Method: This routine adds a data field to a PivotTable. Field is a PivotField object associated with the PivotTable. Caption contains a value that will be used to label the specified PivotField (optional). Function specifies the summary function performed on the data field.

CalculatedFields

Method: returns a CalculatedFields collection, which contains the set of PivotField objects, which are computed based on other fields in the PivotTable.

ColumnFields

Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as column fields.

ColumnGrand

Property: when True, the PivotTable report displays grand totals for each column in the PivotTable object.

DataFields

Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as data fields.

DisplayErrorString

Property: when True, any cells with errors will display the text in the ErrorString property.

DisplayNullString

Property: when True, any cells with null values will display the text in the NullString property.

ErrorString

Property: contains the text that will be displayed in cells with errors, if the DisplayErrorString property is True.

Format (format)

Method: sets the PivotTable's format to one of the formats specified in XlPivotFormatType. Valid formats are xlPTClassic, xlPTNone, xlPTReport1 to xlPTReport10, and xlPTTable1 to xlPTTable10.

HiddenFields

Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are not currently displayed as row, column, page, or data fields.

Name

Property: contains the name of the PivotTable.

NullString

Property: contains the text that will be displayed in cells with null values when the DisplayNullString property is True.

PageFields

Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as page fields.

PivotCache

Method: returns an object reference to the PivotCache object associated with the PivotTable.

PivotFields (index)

Method: returns either the PivotField object specified by the index parameter, or returns the collection of all PivotFields if index is omitted.

PivotTableWizard (SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)

Method: constructs a PivotTable named TableName using data from SourceData and places the result at TableDestination.

RefreshTable

Method: refreshes the data displayed in the PivotTable based on the data in the associated data source.

RowFields

Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as row fields.

RowGrand

Property: when True, displays grand totals for each row in the PivotTable.

VisibleFields

Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed in the PivotTable.

Tip 

Refresh Your Data
Use the RefreshTable method to update the information displayed in the PivotTable if the source data changes.

PivotCaches Collection

The PivotCaches collection contains the set of all PivotCache objects (see Table 16-3). Because PivotCache objects are stored at the workbook level, they can be easily used to create PivotTables and PivotCharts anywhere in the workbook.

Table 16-3: Key Properties and Methods of the PivotCaches Collection

Property/Method

Description

Add(SourceType, SourceData)

Method: adds a new PivotCache object to the collection. SourceType identifies where the data is coming from. Can be xlConsolidation, xlDatabase, xlExternal, xlPivotTable, or xlScenario. SourceData specifies additional information about the source of the data. Typically, this source is a Range object, although in the case of an external database it's a two-element string array, where the first element contains the connection string and the second element contains the SQL query that retrieves the data.

Count

Property (read-only): returns the number of items in the PivotCaches collection.

Item(index)

Method: returns the PivotCache object reference associated with index. Index may refer to the PivotTable by either its name or its relative position in the collection.

PivotCache Object

The data displayed in a PivotTable is stored in the PivotCache object. You can reference the PivotCache through the PivotCache method associated with the PivotTable object or through the PivotCache collection associated with the Workbook object. Multiple PivotTables can share the same PivotCache object.

Table 16-4 contains a list of the key properties and methods associated with the PivotCache object.

Table 16-4: Key Properties and Methods of the PivotCache Object

Property/Method

Description

CommandText

Property: contains a database command string used to retrieve data from an external database.

CommandType

Property: identifies the type of data stored in CommandText using a constant found in the XlCmdType enumeration. Valid command types are xlCmdCube, xlCmdDefault, xlCmdList, xlCmdSql, and xlCmdTable.

Connection

Property: contains an OLE DB or ODBC connection string used to access an external database; may also contain a URL to connect a Web data source or the fully qualified name of a text file or Access database. Use the Refresh method to update the data contained in the data source.

CreatePivotTable (TableDestination, TableName, ReadData, DefaultVersion)

Method: creates a PivotTable object based on the current PivotCache object.

Index

Property (read-only): returns the index number of the PivotCache within the PivotCaches collection.

IsConnected

Property (read-only): when True, means that the PivotCache is currently connected to a data source.

MakeConnection

Method: opens a connection between the PivotCache and the data source specified in the Connection property.

MemoryUsed

Property (read-only): returns the number of bytes used by the current PivotCache object.

OLAP

Property (read-only): returns True when the cache is connected to an OLAP server.

QueryType

Property (read-only): returns the type of query as enumerated in XlQueryType that is used to populate the cache. Value query types are xlADORecordset, xlDAORecordset, xlDDBCQuery, xlOLEDBQuery, xlTextImport, and xlWebQuery.

RecordCount

Property (read-only): returns the number of records in the cache.

Recordset

Property: contains the Recordset object that will be used to populate the cache.

Refresh

Method: updates the information in the PivotCache object and the associated PivotTable object.

RefreshDate

Property (read-only): returns the date when the cache was last refreshed.

RefreshPeriod

Property: contains the number of minutes between refreshes. Setting this property to zero disables automatic refreshes.

PivotField Objects

The PivotField object represents one of the fields in a PivotTable. This object contains a lot of useful information that describes the field, including its Name, MemoryUsed, and NumberFormat. However, the most important properties are the Orientation and Position properties. The Orientation property determines the area on the PivotTable where the field is located, and the Position property determines the order of the field within a particular area.

The PivotItems method returns a collection of PivotItem objects. Each PivotItem object represents a specific value within the PivotField object.

You can access a PivotField object through these properties associated with the PivotTable object. These properties provide a shortcut to commonly used subsets of the data.

  • The PivotFields collection contains the complete set of all PivotField objects associated with the PivotTable.

  • The HiddenFields property returns the set of PivotField objects that are not currently displayed on the PivotTable or the PivotChart.

  • The VisibleFields property returns the set of PivotField objects that are currently displayed on the PivotTable or the PivotChart.

  • The ColumnFields property returns the set of PivotField objects that are displayed as columns in a PivotTable or a PivotChart.

  • The RowFields property returns the set of PivotField objects that are displayed as rows in a PivotTable or a PivotChart.

  • The PageFields property returns the set of PivotField objects that are displayed in the page area of a PivotTable or a PivotChart.

  • The DataFields property returns the set of PivotField objects that are displayed in the data area of a PivotTable or a PivotChart.

  • The CalculatedFields collection contains the set of PivotField objects that are computed from other fields in the PivotTable.

    Note 

    If there is only one PivotField in a collection, the associated property from the PivotTable object will point directly to the PivotField object rather than to a PivotFields collection.

Table 16-5 contains a list of the key properties and methods associated with the PivotField object.

Table 16-5: Key Properties and Methods of the PivotField Object

Property/Method

Description

Caption

Property (read-only): returns the label text for the field.

CurrentPage

Property: contains the current page for a page field.

DataRange

Property (read-only): returns a Range object with the data contained in the field.

DataType

Property (read-only): returns the type of data from the XlPivotFieldDataType enumeration (xlDate, xlNumber, or xlText) that represents the field.

DragToColumn

Property: when False, means that the field cannot be dragged to the column area in the PivotTable.

DragToData

Property: when False, means that the field can't be dragged to the data area in the PivotTable.

DragToHide

Property: when False, means that the field can't be dragged away from the PivotTable.

DragToPage

Property: when False, means that the field can't be dragged to the page area of the PivotTable.

DragToRow

Property: when False, means that the field cannot be dragged to the row area in the PivotTable.

LayoutForm

Property: contains the way that PivotTable items appear. xlTabular implies table format; xlOutline specifies outline format.

MemoryUsed

Property (read-only): returns the number of bytes used by the current PivotField object.

Name

Property: contains the name of the PivotField object.

NumberFormat

Property: contains the formatting specifications that will be used to display the information from the PivotField object. Applies only to fields used in the data area of the PivotTable.

Orientation

Property: contains the location of the field in a PivotTable as specified by the XlPivotFieldOrientation type (xlColumnField, xlDataField, xlHidden, xlPageField, or xlRowField).

PivotItems

Method: returns the collection of PivotItems representing the individual items within a particular field.

Position

Property: contains the relative position of the field within its orientation.

ShowAllItems

Property: when True, means that all items in the PivotTable report will be displayed even if they don't contain summary data.

Value

Property: contains the name of the specified field in the PivotTable report.

PivotItem Object

The PivotItem object represents a single, specific value for a particular PivotField object. Table 16-6 contains a list of the key properties and methods associated with the PivotItem object.

Table 16-6: Key Properties and Methods of the PivotItem Object

Property/Method

Description

Caption

Property (read-only): returns the label text for the field.

DataRange

Property (read-only): returns a Range object with the data contained in the field.

Formula

Property: contains the formula associated with the item in A1- style notation. If the cell is empty, an empty string will be returned. If the cell contains a constant, the constant will be returned.

LabelRange

Property (read-only): returns the Range object representing the cells in the PivotTable report that contain the item.

Name

Property: contains the name of the PivotItem object.

Position

Property: contains the relative position of the field within its orientation.

RecordCount

Property (read-only): returns the number of records in the PivotCache containing the specified item.

Value

Property: contains the name of the specified item in the PivotTable field.

Visible

Property: when True, means that the item is visible.



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net