有没有办法添加vba宏代码到excel？ [英] Is there a way to add vba macro code to excel?

问题描述

我有一个现有的宏函数有问题，所以我要做的是将一个宏传递给excel我的意思是添加函数（Ex：a（））到excel，并运行函数（a（） ）添加。

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

如何做？

提前感谢。 >

解决方案

• 安装主互操作程序集合可用于您的办公室版本



• 添加对Assemblies的引用 - > Extensions - > Microsoft.Office.Interop.Excel和Microsoft.VBE.Interop

    using System ; 
  使用System.Diagnostics; 
  使用System.Windows.Forms; 
  使用Microsoft.Vbe.Interop; 
  使用ExcelInterop = Microsoft.Office.Interop.Excel; 
   
  命名空间WindowsFormsApplication1 
   {
   public partial class AddExcelMacro：Form 
   {
   public AddExcelMacro（）
   {
   InitializeComponent（） ; 
   AddMacro（）; 
  } 
   
   public void AddMacro（）
   {
   try 
   {
   //打开excel文件
   const string excelFile = @ C：\temp\VBA\test.xlsm; 
   var excelApplication = new ExcelInterop.Application {Visible = true}; 
   var targetExcelFile = excelApplication.Workbooks.Open（excelFile）; 
   
   //将标准模块添加到文件
   var newStandardModule = targetExcelFile.VBProject.VBComponents.Add（vbext_ComponentType.vbext_ct_StdModule）; 
   var codeModule = newStandardModule.CodeModule; 
   
   //将vba代码添加到模块
   var lineNum = codeModule.CountOfLines + 1; 
   var macroName =Button1_Click; 
   var codeText =Public Sub+ macroName +（）+\r\\\
  ; 
   codeText + =MsgBox \嗨来自Excel \+\\\\ 
   codeText + =End Sub; 
   
   codeModule.InsertLines（lineNum，codeText）; 
   targetExcelFile.Save（）; 
   
   //运行宏
   var macro = string.Format（{0}！{1}。{2}，targetExcelFile.Name，newStandardModule.Name，macroName）; 
   excelApplication.Run（宏）; 
   
   excelApplication.Quit（）; 
   
  } 
   catch（Exception ex）
   {
   Debug.WriteLine（ex.Message）; 
   throw; 
  } 
  } 
  } 
  } 
    

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.

解决方案

• 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;
              }
          }
      }
  }
  

这篇关于有没有办法添加vba宏代码到excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！