Hi there,
Is there any way of opening an excel file and refresh the data in a
worksheet that is generated by a bunch of macros?
I've some third-party comercial macros that I want to execute in
order to update my excel worksheet but I need to do this
programmatically and automatically once per day. Any hints?
Thanks very much in advance for your time.
Best regards,
Luis Pedro
First you need to download and install the PIA (Primary Inter-op
Assemblies) for Office. They may already be installed, but it's worth
mentioning.
You then need to add a reference to the PIA, you'll find it under the
COM tab in references, scroll down to Microsoft Excel 11 object
library, it should also add a reference to Microsoft.Office.Core.
Then you need a bit of code, something like the following.
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new
Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook book = app.Workbooks.Open(@"C:
\book1.xls",missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing,
missing);
app.Calculate();
book.Save();
Notice the missings. COM allows optional parameters, C# doesn't, so we
replace all the optional parameters with missing.
The interop assemblies are a bit messy. As an example, if you want to
set the value of a cell, you use the Value2 property of a range... The
best way if you're getting to grips with it is to record a macro in
Excel that does what you want and then it's fairly straight forward to
convert that to dotnet. You just need the method names generally.
