c# - Is there a way to add vba macro code to excel?

Question

I have a problem with a existing macro function,so what I have to do is to pass a macro to excel I mean add the function(Ex: a()) to excel,and run the function("a()") added.

Note:I have path of the excel where i`ll have to add macro into. Please be clear about the answers.

How to do this??

Thanks in advance.

Answer 1

• install Primary Interop Assemblies Redistributable for your office version

• Add reference to Assemblies -> Extensions ->Microsoft.Office.Interop.Excel and Microsoft.VBE.Interop

  using System;
  using System.Diagnostics;
  using System.Windows.Forms;
  using Microsoft.Vbe.Interop;
  using ExcelInterop = Microsoft.Office.Interop.Excel;
  
  namespace WindowsFormsApplication1
  {
      public partial class AddExcelMacro : Form
      {
          public AddExcelMacro()
          {
              InitializeComponent();
              AddMacro();
          }
  
          public void AddMacro()
          {
              try
              {
                  // open excel file 
                  const string excelFile = @"c:empVBAest.xlsm";
                  var excelApplication = new ExcelInterop.Application { Visible = true };
                  var targetExcelFile = excelApplication.Workbooks.Open(excelFile);
  
                  // add standart module to file
                  var newStandardModule = targetExcelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
                  var codeModule = newStandardModule.CodeModule;
  
                  // add vba code to module
                  var lineNum = codeModule.CountOfLines + 1;
                  var macroName = "Button1_Click";
                  var codeText = "Public Sub " + macroName +"()" + "
  ";
                  codeText += "  MsgBox "Hi from Excel"" + "
  ";
                  codeText += "End Sub";
  
                  codeModule.InsertLines(lineNum, codeText);
                  targetExcelFile.Save();
  
                  // run the macro
                  var macro = string.Format("{0}!{1}.{2}", targetExcelFile.Name, newStandardModule.Name, macroName);
                  excelApplication.Run(macro);
  
                  excelApplication.Quit();
  
              }
              catch (Exception ex)
              {
                  Debug.WriteLine(ex.Message);
                  throw;
              }
          }
      }
  }