inset vba module using vba

[michael.beckinsale]

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.

 

[Gareth]

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

 

[Bob Phillips]

ActiveWorkbook.VBProject.VBComponents.Import Filename:="C:\myFile.bas"


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[davidm]

I would rather put such a macro in my Personal.xls Workbook and run th
show from such focal point

 

[Gareth]

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.

 

[Tom Ogilvy]

And just as an added Specific reference/URL see Chip Pearson's page on this

http://www.cpearson.com/excel/vbe.htm

 

[Dave Peterson]

This may not be an option, but if you're touching 50 different workbooks, you
may want to consider putting the code into an addin and sharing that addin.

Then when the code needs to be changed, you fix the addin--instead of trying to
fix all 50 workbooks.

 

[michael.beckinsale]

Hi All,

Many thanks for all your prompt responses.

I will digest all the information you have provided and try out the
code ASAP.