Submitted By: Tom Reid

Programmatically adds a macro module and associated VBA code to an existing Excel spreadsheet.

Visual Basic
Edit|Remove
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open("C:\scripts\test.xls")
       Set xlmodule = objworkbook.VBProject.VBComponents.Add(1) 
       strCode = _
       "sub test()" & vbCr & _
       "   msgbox ""Inside the macro"" " & vbCr & _
       "end sub"
       xlmodule.CodeModule.AddFromString strCode
objWorkbook.SaveAs "c:\scripts\test.xls"
objExcel.Quit