Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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()

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    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)!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    [quote name='HansV' post='769054' date='03-Apr-2009 15:03']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[/quote]

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks for checking - I don't have a copy of Excel 2000 (I jumped from 97 to 2002).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •