Generate Excel Spreadsheet with Macros using Microsoft Access

Question

While generating hundreds of Office Excel spreadsheets with Office Access is certainly possible, it would be great to add macros to the generated workbooks.

I would like to add the functions to the object "ThisWorkbook" in the VBA project for each spreadsheet on generation. How would one go about doing this?

Thank you in advance!

Answer 1

Under the assumption that the macro's in all generated workbooks are the same,

• create a template containing all VBA code (and optionally constant text like headers, footers, print range definitions, etc. - i.e. "everything except data")
• create any new workbook from the template
• insert your data into the WB object
• save as macro enabled worksheet (Excel 2007/2010)
• close it

example

Sub CreateWB()
Dim WB As Workbook

    Set WB = Workbooks.Add("MacroTemp.xltm")   ' contains VBA, ActiveX, etc.
    WB.Worksheets("Sheet1").[A1] = "co-cooo!"  ' adding data
    WB.SaveAs "MyGenWB", xlOpenXMLWorkbookMacroEnabled
    WB.Close
End Sub

In Excel 2007/2010 do not forget to save the template as macro enabled template (*.xltm").