none
Can't get pivot table initial data after changing data manually RRS feed

  • Question

  • Hi,

    I am using C# to create a pivot table from a worksheet data source. The pivot table is created correctly. 

    In Excel, I change manually a cell content in the created pivot table then I try to get initial data by changing data source from pivottable tools in Excel and refreshing the pivottable.

    The problem: my pivottable can't get the initial data even if the data source are correct.

    So my questions are:

    1) What I should do as manipulation in excel in order to get initial data afetr changing it manually in the pivot table?

    2) Is someone know how to do this programatically using c#?

    This is the code I have used: 

    Workbook wb = this.myApp.ActiveWorkbook as Workbook;
    String rangInR1C2 = this.dataWorkSheet.Name + "!" + myApp.ConvertFormula(dataRange.Address, XlReferenceStyle.xlA1, XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing) as String;
    PivotCache newPivotCache = wb.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, rangInR1C2, XlPivotTableVersionList.xlPivotTableVersionCurrent);
    newPivotCache.RefreshOnFileOpen = false;
    pivotTables.Item(i).ChangePivotCache(newPivotCache); 



    said

    Tuesday, December 16, 2014 9:54 AM

Answers

  • Hi Said Ouarrich,

    Thanks for the detail expliantation.

    As far as I know, there is no Object model we can ahicve the goal directly. However as a workaround, we can clean the table and add the pivot fileds again. Here is the code for your reference:

       ActiveSheet.PivotTables("PivotTable1").ClearTable
    'add the pivot fileds here again
    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.

    • Marked as answer by saidOuarrich Thursday, December 18, 2014 10:20 AM
    Thursday, December 18, 2014 2:28 AM
    Moderator

All replies

  • Hi saidOuarrich,

    From the description, you are changing the data in an pivottable. As far as I know, the pivottable is a tool used to analyze and present the data. Usuall we change the datasource and referesh the pivottable rather than change the data in pivot table.

    I am not able to understand the secnario exactly, can you explian more about it? Here is an article about PivotTable may be helpful:
    Create a PivotTable to analyze worksheet data

    If I misunderstood, please feel free to let me know.

    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, December 17, 2014 2:52 AM
    Moderator
  • Hi Fei Xue,

    Thank you for the response.

    These are the steps I have followed:

    1. I have placed datasource in the first sheet of my workbook .
    2. I have Created a pivottable in the second sheet. At this point I have no problem because the pivottable is created correctly.
    3. I have changed some data in the pivottable. At this moment I can see that pivottable data are different from datasource.
    4. Now I am trying to make my pivottable show the same data as the datasource. So I click somewhere in my pivottable, I go to Pivottable Tools> Analyse> Change Data Source, I select datasource range then I click Refresh but nothing is changed: my pivottable doesn't show the same data as the datasource. 

    So my question is: Is there a way to make my pivottable shows the same data as the datasource?


    said

    Wednesday, December 17, 2014 9:48 AM
  • Hi Said Ouarrich,

    Thanks for the detail expliantation.

    As far as I know, there is no Object model we can ahicve the goal directly. However as a workaround, we can clean the table and add the pivot fileds again. Here is the code for your reference:

       ActiveSheet.PivotTables("PivotTable1").ClearTable
    'add the pivot fileds here again
    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.

    • Marked as answer by saidOuarrich Thursday, December 18, 2014 10:20 AM
    Thursday, December 18, 2014 2:28 AM
    Moderator
  • Hi Fei Xue,

    Thank you for this solution.

    The data I have changed manualy is no more present.

    Now I should create again the full table after clearing it.

    Thanks again.


    said

    Thursday, December 18, 2014 10:24 AM