insert a sub into an excel spreadsheet with vbs

[sumgirl]

Anyone know how I can add a sub or function to an existing Excel spreadsheet from vbs? I dont want to have to open all the spreadsheets in order to do it.

Thanks.

 

[taupirho]

Hi this should work for you - just change the paths/names of the spreadsheets and macro contents as required


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\Test1.xls")
Set xlmodule = objworkbook.VBProject.VBComponents.Add(1)
strCode = _
"sub MyMacro()" & vbCr & _
" msgbox ""Inside generated macro!!!"" " & vbCr & _
"end sub"
xlmodule.CodeModule.AddFromString strCode

objworkbook.saveas "c:\temp\test2.xls"
objworkbook.close

 

[sumgirl]

Thanks Taupirho.