none
Extract Pivot table Souce Data into .NET datatable RRS feed

  • Question

  • I have a excel file contains pivot table and the source data of the pivot table is no longer in the excel workbook. The only option to get this source data by right click and select "Show Details" option which will create a separate work sheet and show the data.

    My requirement is to extract this source data and upload in to the database.

    Appreciate your help on this.

    Monday, November 11, 2013 10:42 PM

Answers

  • Thanks for the reply.

    I've achieved source data via below code.  This will generate the source data and create in a new sheet. So I use that sheet to extract the data.

    I know this is not the correct way, but it works, if you have better way let me know.

    xlRange = xlPvt.DataBodyRange


    xlRange.Range(xlSh.Cells(xlRange.Rows.Count, 1), xlSh.Cells(xlRange.Rows.Count, xlRange.Columns.Count)).ShowDetail = True
    ' Sheet

    xlWB.ActiveSheet.Range("A1").Select()

    xlDataSheet = xlWB.ActiveSheet.Name

    Wednesday, November 13, 2013 8:59 AM

All replies

  • Hi,

    According to your description, you want to extract the source data of Pivot Table in Excel into a database.

    What do you mean “the source data of the pivot table is no longer in the excel workbook”? Which kind of source data are you using, external data source or inner data source?

    I create a table in the sheet1 like figure below and insert a pivot table base on the table created before. Then I click the show details, it do create a new worksheet however there is no data in it. Did I miss anything?

    We can get the source data from PivotTable.sSourceData Property.

    If the source data in the same workbook, it will return the cell reference. And we can find the source data through the reference. However in this case, the source data is no longer in the workbook. So we can hardly get the source data.

    If the source data is an external data source, you can get the data from external data source like (SQL server, Access etc.).

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 12, 2013 8:57 AM
    Moderator
  • Thanks for your reply.

    Your understanding is correct, The original data no longer on the work book and only pivot table is available.

    I have no access to the main data source. As you mentioned it shows the data in the new sheet when selecting show details menu. So, I want to get that source data into .net data table for further processing.

    Can you provide some sample for PivotTable.SoureData property? I'm always getting message"Exception from HRESULT: 0x800A03EC" while accessing this property.

    Thank you.

    Tuesday, November 12, 2013 9:18 AM
  • Hi,

    >> As you mentioned it shows the data in the new sheet when selecting show details menu. So, I want to get that source data into .net data table for further processing<<

    It create a new worksheet however it doesn’t show any data only the header of table. Do I miss anything?

    >>Can you provide some sample for PivotTable.SoureData property<<

    You can refer to code below to show the source data of PivotTable:

      

    int index = 1;
    
                Excel.PivotTable pt = Globals.ThisAddIn.Application.ActiveSheet.PivotTables(index) as Excel.PivotTable;
    
                MessageBox.Show(pt.SourceData);
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 13, 2013 8:48 AM
    Moderator
  • Thanks for the reply.

    I've achieved source data via below code.  This will generate the source data and create in a new sheet. So I use that sheet to extract the data.

    I know this is not the correct way, but it works, if you have better way let me know.

    xlRange = xlPvt.DataBodyRange


    xlRange.Range(xlSh.Cells(xlRange.Rows.Count, 1), xlSh.Cells(xlRange.Rows.Count, xlRange.Columns.Count)).ShowDetail = True
    ' Sheet

    xlWB.ActiveSheet.Range("A1").Select()

    xlDataSheet = xlWB.ActiveSheet.Name

    Wednesday, November 13, 2013 8:59 AM