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?

Any links,code snippets or examples would be appreciated.

Mark

Answer 1

Probably the simplest way would be to manually create an Excel document containing the desired macros. This document would then server as a template for your VBScript.

Instead of creating a new file you would copy this template, open it in Excel and the populate it with the data using your VBScript.

A more complex and flexible option would be to use the Excel object model. Look for the ActiveWorkbook.VBProject.VBComponents object which will allow you to add forms, modules and classes to the VBA project of the current document. However, this option requires certain security settings (basically you have to allow programmatic access to the VBA project).

Answer 2

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