我有一个现有的宏函数问题,所以我需要将宏传递给Excel,也就是添加函数(例如:a())到Excel中,并运行添加的函数("a()")。
注意:我已经知道了要添加宏的Excel路径。请确保回答清晰明了。
如何操作?
提前感谢您的帮助。
注意:我已经知道了要添加宏的Excel路径。请确保回答清晰明了。
如何操作?
提前感谢您的帮助。
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;
}
}
}
}