' attach VBA Code to a workbook in Excel
' taken from Example07.vb
' Maybe you have to change the excel security settings to
' access the VBE in excel or you get an exception. A good
' tutorial to work with VBE: http://www.cpearson.com/excel/vbe.aspx
Private Sub Example07Main() Dim workbookFile As String = "" Dim excelApplication As Excel.Application = Nothing Try ' start excel and turn off msg boxes excelApplication = New Excel.Application() excelApplication.DisplayAlerts = False excelApplication.Visible = False ' add a new workbook Dim workBook As Excel.Workbook = excelApplication.Workbooks.Add() ' add new global Code Module Dim globalModule As VBE.VBComponent = 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)" & vbNewLine & _
" MsgBox ""Hello World!"" & vbnewline & Param" & vbNewLine & "End Sub") ' create a click event trigger for the first worksheet Dim linePosition As Integer = _
workBook.VBProject.VBComponents.Item(2).CodeModule.CreateEventProc( _
"BeforeDoubleClick", "Worksheet") workBook.VBProject.VBComponents.Item(2).CodeModule.InsertLines( _
linePosition + 1, "HelloWorld ""BeforeDoubleClick""") ' display info in the worksheet Dim sheet As Excel.Worksheet = workBook.Worksheets(1) sheet.Cells(2, 2).Value = "This workbook contains dynamic created VBA Moduls" 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." ' save the book Dim fileExtension As String = GetDefaultExtension(excelApplication) workbookFile = String.Format("{0}\Example07{1}", _
Application.StartupPath, fileExtension)
workBook.SaveAs(workbookFile, Missing.Value, Missing.Value, Missing.Value, _

Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive) Catch throwedException As Exception Dim message As String = String.Format("An error is occured.{0}ExceptionTrace:{0}", _
Environment.NewLine) Dim exception As Exception = throwedException While (Not IsNothing(exception)) message += String.Format("{0}{1}", exception.Message, Environment.NewLine) exception = exception.InnerException End While MessageBox.Show(message) Finally ' close excel and dispose reference excelApplication.Quit() excelApplication.Dispose() If (Not IsNothing(workbookFile)) Then Dim fDialog As FinishDialog = New FinishDialog("Workbook saved.", workbookFile) fDialog.ShowDialog(Me) End If End Try End Sub ''' <summary> ''' returns the valid file extension for the instance. for example ".xls" or ".xlsm" ''' </summary> ''' <param name="application">the instance</param> ''' <returns>the extension</returns> ''' <remarks></remarks> Private Function GetDefaultExtension(ByVal app As Excel.Application) As String Dim version As Double = Convert.ToDouble(app.Version, CultureInfo.InvariantCulture) If (version >= 12.0) Then Return ".xlsm" Else Return ".xls" End If End Function

Last edited May 14, 2012 at 5:53 AM by SebastianDotNet, version 9


No comments yet.