Applies To
PivotTable Object.
Description
Returns the data source for the PivotTable, as shown in the following table.
|
Data Source |
Return Value |
|
Microsoft Excel list or database |
The cell reference as text. |
|
External data source |
An array. Each row consists of a SQL connection string with the remaining elements as the query string broken down into 200-character segments. |
|
Multiple Consolidation ranges |
A two-dimensional array. Each row consists of a reference and associated page field items. |
|
Another PivotTable |
One of the above three kinds of information. |
Example
Assume that you used an external data source to create a PivotTable on Sheet1. This example inserts the SQL connection string and query string into a new worksheet.
Set newSheet = ActiveWorkbook.Worksheets.Add
sdArray = Worksheets("Sheet1").UsedRange.PivotTable.SourceData
For i = LBound(sdArray) To UBound(sdArray)
newSheet.Cells(i, 1) = sdArray(i)
Next i