Running an Excel VBA Macro From SSIS Script Task

[Adam Quark]

Good afternoon, all -

I need to be able to execute a collection of Excel macros from within an SSIS package.

I found an example script online, but it's not a whole lot of help. But, it's small and I figured it would be easier to troubleshoot than the monsters I saw elsewhere that mostly used a Try/Catch method that I am completely unfamiliar with.

Anyway, the snippet of code is as follows;


oExcel = CreateObject("Excel.Application")
//oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString())
//Your macro here:
oExcel.Run("Yourmacro")


I've added references to:

• Microsoft Excel 16.0 Object Library
• Microsoft Office 16.0 Object Library

I've also included the following to Namespaces;


using Excel = Microsoft.Office.Interop.Excel;


The code generated errors with oExcel, oBooks and oBook that I resolved with the suggested corrections Vsta provided; namely, to precede each with object, like so;



object oExcel = CreateObject("Excel.Application");
//oExcel.Visible = False;
object oBooks = oExcel.Workbooks;
object oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString());
//Your macro here:
oExcel.Run("Yourmacro");

But, that left me with the red squiggles under CreateObject, Workbooks, Open, Variables and Run;



Hovering the cursor over each gave me;

• CreateObject: The name 'CreateObject' does not exist in the current context
• Workbooks: 'object' does not contain a definition for 'Workbooks'
• Open: 'object' does not contain a definition for 'Open'
• Variables: Non-invocable member 'ScriptObjectModel.Variables' cannot be used like a method.
• Run: 'object' does not contain a definition for 'Run'

The associated popup solutions that each displayed were outside my ken and not easily interpreted by this self-taught mind. My last C class was back in the 90s and was only a small part of a class that encompassed several other languages. Prior to that, my skills were in Fortran and Basic, but they are woefully old and not at all up to snuff for what I'm trying to do here.

Note: "filepath" and "Yourmacro" are populated, but that made no difference, so I left the placeholders in.

What is it I'm doing wrong? I had the impression from many hits on my search that this isn't a hard thing to do. In fact, it seems to be so simple that many basic things are left off - for example, in the code snippet above, the lines did not have ending semi-colons, but those, at least, even I knew about.

Thanx in advance for any assistance!

Continue reading...