VBScript to Write a Macro within an Excel file

Question

Is it possible for the VBScript to write a Macro within the excel file when it is generated that would be able to run whenever the file is opened?

Ive got a VBScript to auto generate an excel file and some data within it, this runs once and generates the file with all the data/sorts etc.

What I need is to have some interactivity that would normally be run with a macro whilst the program is running and someone views the data, problem here being as it is an auto generated file, no macros are saved.

If so how would this be implemented, how can a macro to be run later be written in the vbscript?

Answer 1

Great advice divo, yes I could create it from a template but it is a case where I would need more flexability and integration with variables in the script.

The excel object model object VBProject.VBComponents works like a charm and does exactly what I was looking for, for the purpose of anyone else looking for a similar answer I found this while looking into the VBProject object:

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

Sources: Scripting Guy Add a Macro to an Excel Spreadsheet

String for excel macro creation