Excel "Refresh All" with OpenXML

Question

Hello,

I have an excel 2007 file (OpenXML format) with a connection to an xml file. This connection generates an excel table and pivot charts are using this table.

I am trying to find a way with OpenXML SDK v2 to do the same as the "Refresh All" button in Excel. So that I could automatically update my file as soon as a new xml file is provided. So I would need to firstly refresh the table thanks, then refresh pivot tables.

I found absolutely nothing with google during hours. I hope anyone will be able to give me good indications.

Thank you.

Answer 1

I think the only way you can do this is following this type of method..

1. Save Open XML workbook back to a xlsx file.
2. Load the workbook using the Excel object model.

3. Call either

   ThisWorkbook.PivotCaches(yourIndex).Refresh

or

ThisWorkbook.RefreshAll 

although I was pretty sure RefreshAll would also work.
4. Use the object model to Save the workbook and close it.
5. Reopen for use with xml namespaces.