Excel “Refresh All” with OpenXML

0 votes
asked Oct 11, 2010 by julio-guerra

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

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.

Thank you.

2 Answers

0 votes
answered Jan 19, 2010 by anonymous-type

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.

  1. Use the object model to Save the workbook and close it.
  2. Reopen for use with xml namespaces.
0 votes
answered Jan 22, 2011 by samuel-neff

You can't do this with Open XML. Open XML allows you to work with the data stored in the file and change the data and formulas and definitions and such. It doesn't actually do any calculations.

Excel automation technically would work, but it's absolutely not recommended for a server environment and is best avoided on the desktop if at all possible.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...