inset vba module using vba

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.

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

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.
>

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


--

HTH

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


"michael.beckinsale" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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.
>>

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

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

--
Regards,
Tom Ogilvy

"michael.beckinsale" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" 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.

--

Dave Peterson

Hi All,

Many thanks for all your prompt responses.

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



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.