VBA执行分几种：
using System; using Excel = Microsoft.Office.Interop.Excel; using System.IO; using System.Diagnostics; using Microsoft.Vbe.Interop; using Microsoft.Office.Interop.Excel; using System.Text; using VBIDE = Microsoft.Vbe.Interop; namespace ExcelVBA { public class VBAHelper { /// <summary> /// 执行Excel中的宏 /// </summary> /// <param name="excelFilePath">Excel文件路径</param> /// <param name="macroName">宏名称</param> /// <param name="parameters">宏参数组</param> /// <param name="rtnValue">宏返回值</param> /// <param name="isShowExcel">执行时是否显示Excel</param> public void RunExcelMacro( string excelFilePath, string macroName, object[] parameters, out object rtnValue, bool isShowExcel ) { try { #region 检查入参 // 检查文件是否存在 if (!File.Exists(excelFilePath)) { throw new System.Exception(excelFilePath + " 文件不存在"); } // 检查是否输入宏名称 if (string.IsNullOrEmpty(macroName)) { throw new System.Exception("请输入宏的名称"); } #endregion #region 调用宏处理 // 准备打开Excel文件时的缺省参数对象 object oMissing = System.Reflection.Missing.Value; // 根据参数组是否为空，准备参数组对象 object[] paraObjects; if (parameters == null) { paraObjects = new object[] { macroName }; } else { // 宏参数组长度 int paraLength = parameters.Length; paraObjects = new object[paraLength + 1]; paraObjects[0] = macroName; for (int i = 0; i < paraLength; i++) { paraObjects[i + 1] = parameters[i]; } } // 创建Excel对象示例 Excel.ApplicationClass oExcel = new Excel.ApplicationClass(); // 判断是否要求执行时Excel可见 if (isShowExcel) { // 使创建的对象可见 oExcel.Visible = true; } // 创建Workbooks对象 Excel.Workbooks oBooks = oExcel.Workbooks; // 创建Workbook对象 Excel._Workbook oBook = null; // 打开指定的Excel文件 oBook = oBooks.Open( excelFilePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing ); // 执行Excel中的宏 rtnValue = this.RunMacro(oExcel, paraObjects); // 保存更改 oBook.Save(); // 退出Workbook oBook.Close(false, oMissing, oMissing); #endregion #region 释放对象 // 释放Workbook对象 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); oBook = null; // 释放Workbooks对象 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks); oBooks = null; // 关闭Excel oExcel.Quit(); // 释放Excel对象 System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); oExcel = null; // 调用垃圾回收 GC.Collect(); #endregion } catch (Exception ex) { throw ex; } } /// <summary> /// 执行宏 /// </summary> /// <param name="oApp">Excel对象</param> /// <param name="oRunArgs">参数（第一个参数为指定宏名称，后面为指定宏的参数值）</param> /// <returns>宏返回值</returns> private object RunMacro(object oApp, object[] oRunArgs) { try { // 声明一个返回对象 object objRtn; // 反射方式执行宏 objRtn = oApp.GetType().InvokeMember( "Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs ); // 返回值 return objRtn; } catch (Exception ex) { // 如果有底层异常，抛出底层异常 if (ex.InnerException.Message.ToString().Length > 0) { throw ex.InnerException; } else { throw ex; } } } public void saveExcel() { string filePath = System.Reflection.Assembly.GetExecutingAssembly().Location; //string directoryPath = System.IO.Path.GetDirectoryName(filePath); //string excelPath = System.IO.Path.Combine(directoryPath, "ExcelFile.xlsx"); Excel.Application excelApp = new Excel.Application(); Workbook workbook = excelApp.ActiveWorkbook; workbook.SaveAs("C:\\Users\\username\\Documents\\test.xlsm", XlFileFormat.xlOpenXMLWorkbookMacroEnabled); } //添加宏，执行宏 public void AddMacro() { try { // open excel file const string excelFile = @"C:\Users\Desktop\vba\test1.xlsm"; var excelApplication = new Excel.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; } } //添加透视表选定宏，执行宏 public void AddMacroWatcher() { try { // open excel file const string excelFile = @"C:\Users\Desktop\vba\test2.xlsm"; var excelApplication = new Excel.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 newworksheetModule = targetExcelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_Document); //var newworksheetModule = targetExcelFile.VBProject.VBComponents.Item("Sheet1"); var codeModule = newStandardModule.CodeModule; // var codeModule = newworksheetModule.CodeModule; /**** * * Private Sub Worksheet_SelectionChange(ByVal Target As Range) Open "C:\Users\leo_b\Desktop\testvba3.txt" For Output As #1 Dim cell As Range Dim str As String For Each cell In Selection str = str & cell.Value & vbNewLine Next cell Print #1, str Close #1 MsgBox str + "写入完成" End Sub * ***/ // add vba code to module var lineNum = codeModule.CountOfLines + 1; var macroName = "Worksheet_SelectionChange"; var codeText = "Private Sub " + macroName + "(ByVal Target As Range)" + "\r\n"; // codeText += " Open \"C:\\Users\\leo_b\\Desktop\\testvba3.txt\" For Output As #1" + "\r\n"; codeText += " Open \"" + excelFile + "\" For Output As #1" + "\r\n"; codeText += " Dim cell As Range" + "\r\n"; codeText += " Dim str As String" + "\r\n"; codeText += " For Each cell In Selection" + "\r\n"; codeText += " str = str & cell.Value & vbNewLine" + "\r\n"; codeText += " Next cell" + "\r\n"; codeText += " Print #1, str" + "\r\n"; codeText += " Close #1" + "\r\n"; codeText += " MsgBox \"写入完成1\"" + "\r\n"; codeText += "End Sub"; codeModule.InsertLines(lineNum, codeText); targetExcelFile.Save(); // excelApplication.SheetSelectionChange += Application_SheetSelectionChange; // run the macro var macro = string.Format("{0}!{1}.{2}", targetExcelFile.Name, newStandardModule.Name, macroName); // var macro = string.Format("{0}!{1}.{2}", targetExcelFile.Name, newworksheetModule.Name, macroName); excelApplication.Run(macro); excelApplication.Quit(); } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } } private void Application_SheetSelectionChange(Object sh, Range Target) { // Target.Value = DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss"); // xlRange = (Globals.ThisAddIn.Application.ActiveCell as Excel.Range).CurrentRegion; // 在selectchange事件中运行VBA代码 // ThisWorkbook.Application.Run("VBA代码名称"); } public void vbarun() { //获取当前工作表 // Excel.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet; //创建Excel Application对象 Excel.Application excelApp = new Excel.Application(); //隐藏Excel窗口 excelApp.Visible = false; //打开Excel文件 Workbook workbook = excelApp.Workbooks.Open("C:\\Users\\leo_b\\Desktop\\vba\\test3.xlsm"); //saveExcel(); string excelFile = "C:\\Users\\leo_b\\Desktop\\testvba3.txt"; //获取第一个工作表 Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; // 定义VBA代码 //string vbaCode = "Sub HelloWorld() \n" + "MsgBox "Hello, World!" \n" + "End Sub"; var macroName = "Worksheet_SelectionChange"; var vbaCode = "Private Sub " + macroName + "(ByVal Target As Range)" + "\r\n"; // codeText += " Open \"C:\\Users\\leo_b\\Desktop\\testvba3.txt\" For Output As #1" + "\r\n"; vbaCode += " Open \"" + excelFile + "\" For Output As #1" + "\r\n"; vbaCode += " Dim cell As Range" + "\r\n"; vbaCode += " Dim str As String" + "\r\n"; vbaCode += " For Each cell In Selection" + "\r\n"; vbaCode += " str = str & cell.Value & vbNewLine" + "\r\n"; vbaCode += " Next cell" + "\r\n"; vbaCode += " Print #1, str" + "\r\n"; vbaCode += " Close #1" + "\r\n"; vbaCode += " MsgBox \"写入完成1\"" + "\r\n"; vbaCode += "End Sub"; // 运行VBA代码 worksheet.Parent.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, worksheet.Parent, new object[] { vbaCode }); } public string Reader(string readfile) { //string ExcelPath = System.Reflection.Assembly.GetExecutingAssembly().Location; //int last = ExcelPath.LastIndexOf("\\"); //ExcelPath = ExcelPath.Substring(0, last); //TODO 20230522 合并透视表的数据和具体字段定义信息 try { //data string readfiled = ""; readfiled = readfile + "\\testvba2.txt"; StreamReader sr = new StreamReader(readfiled, Encoding.UTF8); String line; string text = ""; while ((line = sr.ReadLine()) != null) { text += line.ToString(); } //toushibiao string readfilet = ""; readfilet = readfile + "\\testvba3.txt"; StreamReader srp = new StreamReader(readfilet, Encoding.UTF8); String linep; string textp = ""; while ((linep = srp.ReadLine()) != null) { textp += linep.ToString(); } return text + "," + textp; } catch (Exception ex) { return ""; } } /// <summary> /// 取得语句中的VBA /// </summary> /// <param name="vba">整个语句</param> /// <returns>标准的VBA</returns> public string SubstringVBA(string vba) { int sub = vba.IndexOf("```");//vba语句开始索引 int endsub = vba.LastIndexOf("```");//vba语句结束索引 vba = vba.Remove(endsub);//移除vba尾部不相关语句 sub = vba.IndexOf("```");//重新计算vba开头 vba = vba.Substring(sub + 3);//从vba开头拿vba语句 return vba; } /// <summary> /// 执行VBA /// </summary> /// <param name="filePath">路径</param> /// <param name="componentName">模块名称</param> /// <param name="procedureName">事件过程名称</param> /// <param name="vbaCode">vba代码</param> public void ExecuteVbaCode(string filePath, string componentName, string procedureName, string vbaCode) { //componentName = "模块1"; //procedureName = "Button1_Click";//事件过程名称 //vbaCode vba代码 // vbaCode = "Public Sub " + procedureName + "()" + "\r\n"; //vbaCode += " MsgBox \"Hi from Excel of vba\"" + "\r\n"; //vbaCode += "End Sub"; //filePath = @"C:\Users\Desktop\vba\test1.xlsm"; try { Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; //不显示Excel应用程序 Excel.Workbook workbook = excelApp.Workbooks.Open(filePath); VBIDE.VBE vbe = workbook.VBProject.VBE; //新建模块名，不用参数名 var newStandardModule = workbook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule); componentName = newStandardModule.Name; // VBIDE.VBComponent vbComponent = vbe.ActiveVBProject.VBComponents.Item(componentName); //此处为代码模块名称 VBIDE.CodeModule code = vbComponent.CodeModule; var lineNum = code.CountOfLines + 1; code.InsertLines(lineNum + 1, vbaCode); //此处为要插入执行的VBA代码 excelApp.Run(componentName + "." + procedureName); //执行指定代码模块的过程或函数, 此处为代码模块名称和过程或事件名称 workbook.Close(false); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } catch (Exception ex) { //Common.SaveLog("Run VBA Exception:", ex.Message); } } //Sub PrintRangeCoordinateInfo() /// <summary> /// 取得语句中的ProcedureName /// </summary> /// <param name="vbaS">整个语句</param> /// <returns>标准的VBA</returns> public string SubsProcedureName(string vbaS) { //vbaS = "Sub PrintRangeCoordinateInfo()"; int sub = vbaS.IndexOf("Sub");//sub语句开始索引 int endsub = vbaS.IndexOf("()");//ProcedureName语句结束索引 vbaS = vbaS.Remove(endsub);//移除vba尾部不相关语句 sub = vbaS.IndexOf("Sub");//重新计算vba开头 vbaS = vbaS.Substring(sub + 3);//从vba开头拿sub开头语句 return vbaS; } } }