Nearly forgot. You need to insert a reference to "Microsoft Visual Basic
for Applications Extensibility" in the workbook where you're running the
below code. Maybe you can get away with late binding and avoid this but
I've never tried and there's not much to be gained by that approach I
imagine.

Gareth wrote:
Hi Michael,

Yes you can, I use such a method for generating a report and then
inserting event trapping code in the workbook. I've pasted my code below
that you can customise as you see fit.

WARNING: If the code you insert doesn't compile properly Excel is very
likely to crash. So be careful and save your work regularly - or prepare
to cry in frustration.

I believe Chip Pearson covers manipulating VBA thru VBA in depth at
http://www.cpearson.com/excel.htm so you may like to take a look there.


Function fcnInsertVBACodeIntoThisWorkbook(wb As Workbook, _
myFile As String)
Dim myCode As String

' Insert this code into the ThisWorkbook code module
With wb.VBProject.VBComponents(1).CodeModule
.InsertLines 1, "'" 'seems to make it more stable
.AddFromFile myFile
End With

End Function
Function fcnInsertVBACodeIntoNewModule(wb As Workbook, _
myFile As String)
Dim myCode As String
Dim myMod As VBComponent

'create a new code module and write this code there
Set myMod = wb.VBProject.VBComponents.Add(1)
With myMod.CodeModule
.InsertLines 1, "'" 'seems to make it more stable
.AddFromFile myFile
End With
Set myMod = Nothing

End Function

HTH,
Gareth


michael.beckinsale wrote:

Hi All,

Is it possible to insert a VBA module (which has been exported to say
My Documents) into an existing workbook (say myBook1)and then run the
macro in myBook1. I would like to control the operation from a
"Control" workbook so that l can select the source & target multiple
times as l need to add this VBA module to over 50 existing workbooks.

Any idea's / example code greatly appreciated

Regards

Michael beckinsale.