Run a Macro from C#

Question

I am aware you can use Microsoft.Office.Interop.Excel; to use VBA commands inside of a C# program.

I have VBA that is close to 10,000 lines of code and translating that into C# compatible commands is just unrealistic. It creates a workbook and performs data manipulation and formatting to be printed as the user requests.

Is there a way of storing the macro in C# and creating a workbook that I can then run the macro on as is?

I have never used but witn Interop there is Application.Run() method to run a macro. — Cetin Basoz, Aug 17 '16 at 13:20
This might be useful... http://stackoverflow.com/questions/9301354/inject-and-execute-excel-vba-code-into-spreadsheet-received-from-external-source — dazedandconfused, Aug 17 '16 at 13:22
http://stackoverflow.com/questions/38975802/excel-interop-c-sharp-cant-add-vbproject-error-800a03ec-on-workbook-vbproject-v/38978442?noredirect=1#comment65333643_38978442 fairly recent — prizm1, Aug 17 '16 at 13:22

score 2 · Answer 1 · answered Mar 20 '19 at 20:04

Here is example for this solution. I created workbook HelloWorldVBACodeExample.xlsm. Then in this workbook Module1 and VBA code:

Sub HelloWorld(word)

  MsgBox "Hello world and " & word

End Sub

Then created console application with C# code:

using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;
using System;
using System.IO;

namespace ConsoleAppCallVBA
{
    class Program
    {
    static void Main(string[] args)
    {

        string errorMessage = string.

        #region Check is Excel is installed in the PC on which program is executed

        _Application excel = new _Excel.Application();
        if (excel == null)
        {

            errorMessage = "EXCEL could not be started." + "\n" +
                "This program is able to form reports only on PC with installed Excel. " + "\n" +
                "Check that your office installation is correct.";
            Console.WriteLine(errorMessage);
        }

        #endregion

        excel.Visible = true;

        string fileName = "HelloWorldVBACodeExample.xlsm";
        string pathToExcelXlsmFile = Path.Combine(path, fileName);

        Workbook wb;
        Worksheet ws;
        int sheetNumber = 1;
        wb = excel.Workbooks.Open(pathToExcelXlsmFile);
        ws = wb.Worksheets[sheetNumber];

        //Call VBA code
        excel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, excel, new Object[] { "HelloWorldVBACodeExample.xlsm!HelloWorld", "My Name"});


        #region Close excel object - release memory from this application

        excel.Quit();
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);

        #endregion
        }
     }
 }

During execution this C# code executed VBA code and showed in message box text "Helo world and My Name". In the case when this code snippet will be used, to string variable path must be assigned correct path of the directory where will be xlsm file with VBA code.

score 0 · Answer 2 · answered Dec 09 '19 at 14:54

You will have to enable msoAutomationSecurity to low and execute the macro as a string. However, you will probably want to reset the security setting to high after your done using the spreadsheets.

Here is an example:

    public void ExecuteExcelMacro(string sourceFile)
    {
        ExcelApp.Application ExcelApp = new ExcelApp.Application();
        ExcelApp.DisplayAlerts = false;
        ExcelApp.Visible = false;
        ExcelApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
        ExcelApp.Workbook ExcelWorkBook = ExcelApp.Workbooks.Open(sourceFile);

        string macro = "Sheet1.SendEmailToUser";

        try
        {
            ExcelApp.Run(macro);
            Console.WriteLine("Macro: " + macro + " exceuted successfully");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Unable to Run Macro: " + macro + " Exception: " + ex.Message);
        }

        ExcelWorkBook.Close(false);
        ExcelApp.Quit();
        if (ExcelWorkBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); }
        if (ExcelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); }
    }

score 0 · Answer 3 · answered Sep 30 '21 at 04:17

    public void ExecuteMacro()
    {
        string path = Environment.CurrentDirectory;
        string filePath = "";
        string[] fileEntries = Directory.GetFiles(".\\Source");
        foreach (string fileName in fileEntries)
        {
            if (fileName.IndexOf(".xlsm") > 0 && fileName.IndexOf("$")<1) filePath = fileName;
        }

        if (filePath == "") return;

        filePath = filePath.Replace(".\\", "\\");
        string fileDest = filePath.Replace("Source","Processed");
        filePath = path+filePath;
        fileDest = path+fileDest;

        Excel.Application ExcelApp = new Excel.Application();
        Excel.Workbook wb = ExcelApp.Workbooks.Open(filePath, ReadOnly: false);

        try
        {
            ExcelApp.Visible = false;
            ExcelApp.Run("UpdateSheets");

            try
            {
                File.Delete(fileDest);
            }
            catch (Exception) { }

            wb.SaveAs(fileDest);
        }
        catch (Exception) { }

        wb.Close(false);
        ExcelApp.Application.Quit();
        ExcelApp.Quit();
    }

Run a Macro from C#

3 Answers3

Linked