Hi, I have a worksheet containing a ListObject which serves as source for a Pivot table which lies in the same worksheet.
Then I need to copy this worksheet into a new one (Copy or AddCopy methods, the result is the same), but when I open the generated file the PivotTable in the new worksheet is still referencing data from the original sheet from which this was copied.
Afterwards I tried invoking the “ChangeDataSource” method on the newly copied pivottable passing the name of the ListObject which should have served, locally, as new datasource (I’ve also called all the RefreshData and CalculateData methods as mentioned by you in other threads).
The result is that when I try to open the generated file, I get a recovery error by Excel and if I choose to repair, the new PivotTable is still pointing to the original datasource.
Below the example code I used to reproduce the problem
void Main()
{
Workbook wb = new Workbook(“test.xlsx”);
CopyWorkSheet(wb, “NewSheet”);
wb.Save(“output.xlsx”);
}
public static void CopyWorkSheet(Workbook wb, string newWSName)
{
Worksheet sheet = wb.Worksheets[0];
Worksheet newWorksheet = wb.Worksheets.Add(newWSName);
newWorksheet.MoveTo(sheet.Index + 1);
newWorksheet.Copy(sheet);
// Update PivotTable Sources
foreach (PivotTable pt in newWorksheet.PivotTables)
{
string[] newDS = new string[] { “Table2” };
pt.ChangeDataSource(newDS);
pt.RefreshDataOnOpeningFile = true;
}
}