C# running a excel macro with parameters

Question

I am trying to programatically open an excel workbook and run a macro which accepts a parameter typed into the command line. So far, I am able to open the workbook and execute the macro, but I am having trouble passing in the parameter.

My code at the moment:

 public void runTemplate(string templateName, string sourceFile, string destinationFile, string ITPath, string date)
    {
        string template = templateName + "!DoTheImport";
        Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
        ExcelApp.DisplayAlerts = false;
        object misValue = System.Reflection.Missing.Value;
        ExcelApp.Visible = false;
        Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook = ExcelApp.Workbooks.Open(sourceFile);
        RunMacro(ExcelApp, new Object[] { template });
        ExcelWorkBook.SaveCopyAs(destinationFile);
        ExcelWorkBook.SaveCopyAs(ITPath);
        ExcelWorkBook.Close(false, misValue, misValue);
        ExcelApp.Quit();
        if (ExcelWorkBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); }
        if (ExcelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); }
    }

    private void RunMacro(object oApp, object[] oRunArgs)
    {
        oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs);
    }

My Macro looks like:

Sub DoTheImport(sDate As String)


With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;\\filePath\DecisionsByRegion-" + sDate + ".txt", 
    Destination:=Range("$A$2") _)
    .Name = "test"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
Columns("A:G").EntireColumn.AutoFit
Columns("H").EntireColumn.Delete

End Sub

As I said, this works fine for executing a macro (sDate was originally Now() formatted as a string in the sub, not passed in as shown), but I am trying to take the 'date' variable being passed into runTemplate and pass it into my Macro as sDate. I have tried simply adding it into the parameter object RunMacro(ExcelApp, new Object[] { template, date }); but this threw an error.

Incase this helps, a macro is only strictly a Macro if it is a sub and parameterless. Your 'macro with a parameter' is actually a vba function. That might help your googling :) — Davesoft
– Davesoft, Commented Aug 9, 2018 at 14:05
@Davesoft no - both a Sub and Function can have parameters... msdn.microsoft.com/en-us/vba/language-reference-vba/articles/… I think I know what you meant but calling this a Function is incorrect. — BigBen
– BigBen, Commented Aug 9, 2018 at 14:13
This is a social engineering problem, doesn't have much to do with software engineering. That macro look like it was written by somebody that maximized his odds for staying employed. In the USA you'd address this by buying a copy of the New York Times, Sunday edition for extra heft and bringing it along when visiting the author. If he responds "maybe later, I'm busy now" you forcefully apply the newspaper over the head until you get "right away". The macro argument must change from sDate to sFilePath. Now you can simply use File.Exists() in the C# code to fail early. — Hans Passant
– Hans Passant, Commented Aug 13, 2018 at 8:14
@HansPassant Thanks for your suggestion, I did consider this (passing the filepath, not the NYT!). Originally these macros were run manually after a script had been obtained by SQL server, then were eventually automated. The legacy system is designed this way, and a requirement of my work is that it continues to work with the legacy system, so changing the structure of the macro isn't an option. Regardless, there must be a way of achieving what I'm asking. — Barry Piccinni
– Barry Piccinni, Commented Aug 13, 2018 at 8:56
I can't guess how many NYT copies it takes to get somebody to copy that macro and give it a different name. Hard-coding the same path in your C# code so you can still use File.Exists produces an unmaintainable mess, but does provide job security. — Hans Passant
– Hans Passant, Commented Aug 13, 2018 at 9:08

Barry Piccinni · Accepted Answer · 2018-08-13 09:08:44Z

Whilst I have not been able to pass in multiple variables using my existing approach, I have found an alternative method for executing the macro which has allowed me to pass parameters as required.

public void runTemplate(string templateName, string sourceFile, string destinationFile, string ITPath, string date)
{
    string sDate = date;
    Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
    ExcelApp.DisplayAlerts = false;
    object misValue = System.Reflection.Missing.Value;
    ExcelApp.Visible = false;
    Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook = ExcelApp.Workbooks.Open(sourceFile);
    ExcelApp.Run("DoTheImport", sDate);
    ExcelWorkBook.SaveCopyAs(destinationFile);
    ExcelWorkBook.SaveCopyAs(ITPath);
    ExcelWorkBook.Close(false, misValue, misValue);
    ExcelApp.Quit();
    if (ExcelWorkBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); }
    if (ExcelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); }
}

I have removed the RunMacro method and simply used ExcelApp.Run("DoTheImport", sDate); to execute the macro. This method allows me to pass parameters into the macro, which can be accessed in the macro by adding the 'ByVal' parameter passing mechanism:

Sub DoTheImport(ByVal sDate As String)

Collectives™ on Stack Overflow

C# running a excel macro with parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related