VBScript - Add a Macro to an Excel Spreadsheet

VBScript - Add a Macro to an Excel Spreadsheet

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

[Via]