Posted 21 July 2023, 9:48 am EST - Updated 21 July 2023, 9:58 am EST
I have created a spreadsheet via excel with a pivot table on a second sheet based on data in the first sheet.
Programatically I then open that workbook via workbook.Open. I then add values to a known range of cells on the first sheet and save via workbook.Save (I have tried with and without specifying the type as xlsx).
If I then open this saved copy in excel, the pivot table is no longer a pivot table, its just a range of ordinary cells.
I have tried just opening it and then immediately saving it and see the same behavior. I can see the pivot table is there by inspecting the second sheet in my code once it is opened, so the save process is removing/corrupting it.
I need the pivot table to be preserved on the resultant workbook, what do I need to do to ensure this?
Update: I immediately solved my issue. I selected to add the data to the Data Model when adding the pivot table. When this is selected, saving the workbook breaks the data connection. I didn’t need this option, so it seems fine without it.
