Inject and execute Excel VBA code into spreadsheet received from external source

Question

I would like to programatically (in vbscript or command line or whatever) inject VBA code into a workbook I get from an FTP site and then execute the macro on the workbook. All automated. No user interaction.

I have the VBA code defined in a text file.

Answer 1

Haven't had a chance to try this yet but i think this will do what i need.

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