none
Get PivotTable.SourceData for External SQL connection RRS feed

  • Question

  • Have a PivotTable in my Excel sheet which is created from an External SQL connection (.odc file). Now I want to read PivotTable.SourceData for which I tried

    string[] sourceDataArr = pt.SourceData;

    and since its giving exception

    string sourceData = pt.SourceData as string;

    because this documentation says there are 4 types of return values. But I receiving an exception HRESULT: 0x800A03EC.

    How can I get rid of this

    Tuesday, November 3, 2015 8:40 AM

Answers

  • >>>because this documentation says there are 4 types of return values. But I receiving an exception HRESULT:

     0x800A03EC.<<<

    According to your description, I have made a sample with VBA to get PivotTable.SourceData for External SQL connection, unfortunately, I got below error:

    Through my search, I find this sentence "This property is not available for OLE DB data sources.".

    So I suggest that you need to make sure that you didn't use OLE DB data sources when you create PivotTable with External SQL connection.

    Otherwise you could refer to below code that gets PivotTable.SourceData without OLE DB data sources:

    Set nextSheet = ActiveWorkbook.Worksheets.Add
    
    sdArray = Worksheets("Sheet1").PivotTables(1).SourceData
    
    For i = LBound(sdArray) To UBound(sdArray)
    
     nextSheet.Cells(i, 1) = sdArray(i)
    
    Next i

    For more information, click here to refer about PivotTable.SourceData Property (Excel)


    Wednesday, November 4, 2015 9:15 AM