vba 有没有办法将vba宏代码添加到excel？

Is there a way to add vba macro code to excel?

提问by user2002774

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.

我现有的宏函数有问题，所以我要做的是将宏传递给 excel 我的意思是将函数（例如：a（））添加到 excel，然后运行添加的函数（“a（）”） .

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

注意：我有 excel 的路径，我必须在其中添加宏。请清楚答案。

How to do this??

这该怎么做？？

Thanks in advance.

提前致谢。

回答by dee

• 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:\temp\VBA\test.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 +"()" + "\r\n";
                  codeText += "  MsgBox \"Hi from Excel\"" + "\r\n";
                  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;
              }
          }
      }
  }
  

• 为您的办公版本安装可重新分发的主要互操作程序集

• 添加对程序集 -> 扩展 ->Microsoft.Office.Interop.Excel 和 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:\temp\VBA\test.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 +"()" + "\r\n";
                  codeText += "  MsgBox \"Hi from Excel\"" + "\r\n";
                  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;
              }
          }
      }
  }