How to update PivotTable data source using C#?

Question

I would like to know how to update an existing pivot table data source. I am using Microsoft.Office.Interop.Excel and targeting users with Excel 2010.

I am currently able to refresh the pivot table which works fine, however when more rows are added I want to include these rows in the pivot table data source. For example, the pivot table data source when viewed in Excel is DataSourceWorksheet!$A$2:$U$26 and I would like it to be changed to DataSourceWorksheet!$A$2:$U$86 (60 more rows) after my update/refresh excel file code has run.

Here's a simplified version of my code

Application excelApplication = new Application();
Workbooks workbooks = excelApplication.Workbooks;
wrkBook = workbooks.Open(EXCEL_DOCUMENT_PATH, Missing.Value, false, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, true, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);

/* update data source worksheet code here (omitted for example) */

Worksheet pivotWorkSheet = (Worksheet)wrkBook.Sheets[PIVOT_SHEET_NAME];
PivotTable pivot = (PivotTable)pivotWorkSheet.PivotTables(pivotTableName); 

/* I would like to update the pivot tables data source here...

   I could use Range object from data source worksheet...

   Basically just want to tell pivot table, use these cells now... */

pivot.RefreshTable();

/* cleanup code here (omitted for example) */

A solution which may work, would be to just regenerate the pivot table again using wrkBook.PivotTableWizard( ... ) . However, this will not work for my situation since users would prefer to modify their pivot table by changing selected fields, rows, columns, etc. It just needs to be updated when the data source worksheet changes.

Answer 1

its very simple..

pivot.SourceData = "SheetName!R1C1:R18C18"
pivot.RefreshTable();

Thats all..

Remember, always we need to give Row and Column format.

E.g.

"SheetName!" + R + Rowstartingnumber + C + Column StartingNumber  : R + Rowendnumber + C + Column Endnumber.

i.e "SheetName!R1C1:R12C13" like that.

If you give "SheetName!$A$1:$Q$18" like this, it will not work.

Answer 2

The solution to this problem was to use a Dynamic Named Range in excel for the Pivot Table data source. The Dynamic named Range formula uses a combination of the OFFSET function and the COUNTA function like so:

=OFFSET(DataSourceWorksheet!$A$2,0,0,COUNTA(DataSourceWorksheet!$A$2:$A$1000),1)

The above formula will result in a range of cells within A2:A1000 that have data. In my case I required more than one column and I greater range of rows to 'check' for data.

Now in C# to create a named range I used the following:

Names wrkBookNames = wrkBook.Names;
string dynamicRangeFormula = "=OFFSET(DataSourceWorksheet!$A$2,0,0,COUNTA(DataSourceWorksheet!$A$2:$A$1000),1)";
wrkBookNames.Add("MyNamedRange", dynamicRangeFormula );

and then to specify that named range as the data source when creating the new pivot table I simply just pass the string value of the range name into the PivotTableWizard method:

//get range for pivot table destination
Range pivotDestinationRange = pivotWorkSheet.get_Range("A1", Type.Missing);

wrkBook.PivotTableWizard(
                    XlPivotTableSourceType.xlDatabase,
                    "MyNamedRange",
                    pivotDestinationRange,
                    pivotTableName,
                    true,
                    true,
                    true,
                    true,
                    Type.Missing,
                    Type.Missing,
                    false,
                    false,
                    XlOrder.xlDownThenOver,
                    0,
                    Type.Missing,
                    Type.Missing
                    );

Now when rows are added or deleted within the DataSourceWorkSheet the pivot table will only be referencing the cells with data values by using the Named Range. Refreshing the pivot table now just works as expected, by calling RefreshTable:

PivotTable pivot = (PivotTable)pivotWorkSheet.PivotTables(pivotTableName); 

pivot.RefreshTable();

Overall, this would be happening within a method to create a new excel workbook or update an existing workbook. Calling this method for an existing workbook now relies on the fact the the existing pivot table was created using a dynamic named range and will be updated accordingly.