Mapping between Excel and XML file - How to refresh from C#

Question

Hi

Got an excel spread sheet where I specify mapping to an XML file.

I'd like to open the spread sheet from C# and force refresh the cells mappped to the XML file. -> How can I do that ? Any dll I need to reference ?

Thanks

Olivier

Answer 1

Have you had a look at VSTO. VSTO allows you to call .NET code and therefore it might be easier to manage what is happening.

Might be worthwhile taking a peek^^

Answer 2

I am not no sure about what you mean by mapping XML file, but you should be able to use excel PIA and invoke calculate() on each sheet, forcing all cells to refresh.

Answer 3

You have not specified your requirements, but this is fairly easy using the handy "macro recorder" feature of Excel to get the needed "API" and Visual Studio Tools for Office (VSTO) at http://msdn.microsoft.com/en-us/vsto/default.aspx provides the functionality you need in order to use C#.

1. In Excel, record a macro that refreshes the XML Mapping.
2. In C#,
   • Using VSTO, duplicate the macro.
   • Using VSTO, create a project, see what assemblies are referenced, add those references to your project, then duplicate the macro.

Obviously, the devil is in the details, but this will get you started.