Pivot Table – identifying source data type

Topic

New Reply

In Excel 2000, is there any programmatic way of identifying the Type of source data (range, consolidation, external data source) of an existing Pivot Table?

For tables that I know are based on ranges, the SourceData property returns a string representing the range, but SourceData returns other types of object if the data comes from another source and I can’t see how to trap for this!

Reply | Quote

Replies

For a pivot table based on an external data source, the SourceData property is a one-dimensional array with two elements. The first element is the connection string, and the second element is the SQL string.

For a pivot table based on multiple consolidation ranges, the SourceData property is a two-dimensional array, with a row for each consolidation range.
Each row contains two columns; the first is the address of the range as a string, the second is the name of the range as displayed in the Page dropdown.

For both types, TypeName(datasource) returns “Variant()”, while it returns “String” for a standard pivot table. For example:

Debug.Print TypeName(ActiveSheet.PivotTables(1).SourceData)
Variant()

Reply | Quote

Excellent – thanks Hans.

I did find a reference to SourceType via Google but I’m assuming this must be a 2007 enhancement because I couldn’t find it in my work environment (2000) or at home (2003)!

Reply | Quote

SourceType is a property of the PivotCache object, not of the PivotTable object. You can use it like this:

ActiveSheet.PivotTables(1).PivotCache.SourceType

or

ActiveWorkbook.PivotCaches(1).SourceType

Reply | Quote

SourceType is a property of the PivotCache object, not of the PivotTable object. You can use it like this:

ActiveSheet.PivotTables(1).PivotCache.SourceType

or

ActiveWorkbook.PivotCaches(1).SourceType

But I’m still right in thinking it’s not available in 2000 aren’t I????

Reply | Quote

There’s a reference in the Excel 2000 VBA documentation to a SourceType property but it’s not entirely clear whether it applies to the PivotCache object.
It’s definitely present in Excel 2002, and it’s *not* listed as a new property there, which leads me to believe it must have existed in Excel 2000.

Reply | Quote

The PivotCache in XL2000 does NOT have a “SourceType” property. I just checked on my home desktop which has XL2000.

SourceType is a parameter when creating the Pivot table, but I think in XL2000 the ability to see it afterwards is limited (at least I haven’t found it)…

Steve

Reply | Quote

Thanks for checking – I don’t have a copy of Excel 2000 (I jumped from 97 to 2002).

Reply | Quote