Inserting VB code remotely

[PearlGI]

Not sure where to start with this, but I'm sure it's possible.

I've got a db that creates output into a new Excel workbook (that works fine). But now I want to add some code automatically into the ThisWorkbook module of the Excel workbook.

How do I get Access to add VB code to an Excel workbook.

I presume it'll involve something like

objExcel.ActiveWorkbook.VBProject

but that's as far as I've got.


Any help gratefully received.

 

[Fornatian]

Well, this code should get you a bit farther down the road, it shows how to add lines of code to the module.

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines 1, "Private Sub Workbook_Open()"
.InsertLines 2, "Msgbox " & Chr(34) & "Hello" & Chr(34)
.InsertLines 3, "End Sub"
End With

Although I don't know the answer of the top of my head, wuld it work to output data to a new xls based on a pre-existing template workbook with the code already there?

 

[PearlGI]

Many Thanks,

Taken your suggestion of creating the new files based on a pre-existing file with the code already attached. Works perfectly.




Aside of that - couldn't get the code to work. Kept getting error saying "object not open to automation"