private void button1_Click(object sender, EventArgs e) { try { // start excel and turn off msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); // add new global Code Module VBIDE.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 World!\" & 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 workBook.Worksheets[1].Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code"; workBook.Worksheets[1].Cells[5, 2].Value = "Open the VBA Editor to see the code"; workBook.Worksheets[1].Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet."; // save the book string fileExtension = GetDefaultExtension(_excelApplication); string workbookFile = string.Format("{0}\\Example07{1}", Environment.CurrentDirectory, fileExtension); workBook.SaveAs(workbookFile); FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile); fDialog.ShowDialog(this); } catch (System.Reflection.TargetInvocationException throwedException) { string message = string.Format("An error is occured.{0}ExceptionTrace:{0}", Environment.NewLine); Exception exception = throwedException; while (null != exception) { message += string.Format("{0}{1}", exception.Message, Environment.NewLine); exception = exception.InnerException; } MessageBox.Show(message); } finally { // close excel and dispose reference _excelApplication.Quit(); _excelApplication.Dispose(); } }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes excelApplication = new XlApplication(); excelApplication.DisplayAlerts = false; // add a new workbook XlWorkbook workBook = excelApplication.Workbooks.Add(); // add new global Code Module XlVBComponent globalModule = workBook.VBProject.VBComponents.Add(LateBindingApi.Excel.Enums.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 World!\" & 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 workBook.Worksheets[1].Cells(2, 2).Value = "This workbook contains dynamic created VBA Moduls and Event Code"; workBook.Worksheets[1].Cells(5, 2).Value = "Open the VBA Editor to see the code"; workBook.Worksheets[1].Cells(8, 2).Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet."; // save the book string fileExtension = XlConverter.GetDefaultExtension(excelApplication); string workbookFile = string.Format("{0}\\Example7{1}", Environment.CurrentDirectory, fileExtension); workBook.SaveAs(workbookFile); // close excel and dispose reference excelApplication.Quit(); excelApplication.Dispose(); FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile); fDialog.ShowDialog(this); }