public void Import(string projectName) { var target = _environment.ActiveVBProject; using (Stream file = File.OpenRead(BuildFileName(projectName))) { var formatter = new BinaryFormatter { AssemblyFormat = System.Runtime.Serialization.Formatters.FormatterAssemblyStyle.Simple, Binder = new DeserializationAppDomainBinder() }; VbaCodeComponents result = (VbaCodeComponents)formatter.Deserialize(file); foreach (var item in result.Components) { Vbe.VBComponent module = target.VBComponents.FirstOrDefault(e => e.Name == item.Name); if (null == module) { module = target.VBComponents.Add(Vbe.Enums.vbext_ComponentType.vbext_ct_StdModule); module.Name = item.Name; } else { module.CodeModule.DeleteLines(1, module.CodeModule.CountOfLines); } module.CodeModule.AddFromString(item.Code); } } }
public void RunExample() { bool isFailed = false; string workbookFile = null; Excel.Application excelApplication = null; try { // start excel and turn off msg boxes excelApplication = new Excel.Application(); excelApplication.DisplayAlerts = false; excelApplication.Visible = false; // create a utils instance, not need for but helpful to keep the lines of code low Excel.Tools.CommonUtils utils = new Excel.Tools.CommonUtils(excelApplication); // add a new workbook Excel.Workbook workBook = excelApplication.Workbooks.Add(); // add new global Code Module VB.VBComponent globalModule = workBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule); globalModule.Name = "MyNewCodeModule"; // add a new procedure to the modul globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello from NetOffice!\" & vbnewline & Param\r\nEnd Sub"); // create a click event trigger for the first worksheet int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet"); workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\""); // display info in the worksheet Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1]; sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code"; sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code"; sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet."; // save the book XlFileFormat fileFormat = GetFileFormat(excelApplication); workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros); workBook.SaveAs(workbookFile, fileFormat); } catch (System.Runtime.InteropServices.COMException throwedException) { isFailed = true; HostApplication.ShowErrorDialog("VBA Error", throwedException); } finally { // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); if ((null != workbookFile) && (!isFailed)) { HostApplication.ShowFinishDialog(null, workbookFile); } } }