vba - Run an Excel Macro from SSIS

Question

So Im busy making an SSIS package and I need to run a macro in an excel document, I just don't know VB or how I would code this in a Script Task.

I have an excel document called something like DATA.xlsm with a Macro called "Formatting"

I just need to have a script task that runs this formatting macro in DATA.xlsm and then saves the new updated document.

Any help is appreciated.

Ive looked at other posts on this but none of them are really helpful, or seem more complicated than what I am trying to do.

Answer 1

Here is the basic skeleton code in C# to run a macro (you must add a reference to Microsoft.Office.Interop.Excel to make this work)

 Excel.Application xlApp = new Excel.Application();
 Excel.Workbook xlWorkBook = xlApp.Workbooks.Open("C:\ExcelDirectory\DATA.xlsm"); // absolute path needed
 xlApp.Run("Formatting"); // method overloads allow you to send it parameters, etc.
 xlWorkBook.Close(true); // first parameter is SaveChanges
 xlApp.Quit();