Copy Module to new workbook & add button

[JBRTaylor]

Hi
I am trying to copy a module containing a macro from one spreadsheet (copymod.xls) to another workbook (book1.xls) and then insert a button to run the macro I have just copied into book1.

Using the code below the macro runs from the copymod sheet and not the book1 sheet as I would like it to. I am quite new to VBA and don’t fully understand all the code yet.

Any help would be greatly appreciated.
Thanks in advance

Sub copymod()
    Workbooks.Add
    Dim FName As String
With Workbooks("copymod")
    FName = "C:\code.bas"
    .VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName
Kill "C:\code.bas"
    ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5).Select
    Selection.OnAction = "msg"
    Application.CommandBars("Forms").Visible = False
    Range("F8").Select
End Sub

 

[Domenic]

Assuming that copymod.xls is already opened, and that the following code is placed in book1.xls, Module1 from copymod.xls will be imported into book1.xls, and a button will be added to the active sheet in book1.xls...

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> CopyMod()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> FName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Btn <SPAN style="color:#00007F">As</SPAN> Button<br>    <br>    FName = "C:\Users\Domenic\Desktop\code.bas" <SPAN style="color:#007F00">'change the path accordingly</SPAN><br>    <br>    Workbooks("copymod.xls").VBProject.VBComponents("Module1").Export FName<br>    <br>    ThisWorkbook.VBProject.VBComponents.Import FName<br>    <br>    Kill FName<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Btn = ThisWorkbook.ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5)<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Btn<br>        .Caption = "Button Name" <SPAN style="color:#007F00">'change the name of the button accordingly</SPAN><br>        .OnAction = "msg"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[JBRTaylor]

Hi
Thanks for the quick reply. I have tried you solution which is great but i'd like to be able to run the above code from the copymod spreadsheet.
Thanks Jon

 

[Domenic]

Using the code below the macro runs from the copymod sheet and not the book1 sheet as I would like it to.

Sorry, I took the above to mean that you'd like to run the macro from book1.xls. Try the following instead...

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> CopyMod()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> FName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wkbDest <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">Dim</SPAN> Btn <SPAN style="color:#00007F">As</SPAN> Button<br>    <br>    FName = "C:\Users\Domenic\Desktop\code.bas" <SPAN style="color:#007F00">'change the path accordingly</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wkbDest = Workbooks("book1.xls")<br>    <br>    ThisWorkbook.VBProject.VBComponents("Module1").Export FName<br>    <br>    wkbDest.VBProject.VBComponents.Import FName<br>    <br>    Kill FName<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Btn = wkbDest.ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5)<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Btn<br>        .Caption = "Button Name" <SPAN style="color:#007F00">'change the name of the button accordingly</SPAN><br>        .OnAction = "msg"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[pgc01]

Hi

Another way is not to use a temporary file to export/import the code.You can create a new module in the destination workbook and copy the code directly from the one module to the other. This is more efficent and avoids problems with permissions. On the down side, it's (a litte bit) more work.

For ex., using part of Domenic's code:

Option Explicit
 
Sub CopyMod()
Dim wkbSrc As Workbook, wkbDest As Workbook
Dim Btn As Button
Dim s As String
 
' set the source and destination workbooks
Set wkbSrc = Workbooks("Copymod.xlsm")
Set wkbDest = Workbooks("Book1.xlsm")
 
' get the code from the source module
With wkbSrc.VBProject.VBComponents("Module1").CodeModule
    s = .Lines(1, .CountOfLines)
End With
 
' Create the destination module and copy the code
With wkbDest.VBProject.VBComponents.Add(1)
    .Name = "SomeName"
    With .CodeModule
        If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
        .AddFromString s
    End With
End With
 
Set Btn = wkbDest.ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5)
With Btn
    .Caption = "Button Name" 'change the name of the button accordingly
    .OnAction = "msg"
End With
End Sub

Good night

 

[JBRTaylor]

Hi Guys
Thanks for your sugestions. Both suggestions copy the code but the problem appears to be with assigning the macro to the button. Everytime the button refers to the macro in the original copymod workbook and not the new workbook book1. I'd really like to find a way of both copying the code across to the new workbook AND attaching the code (which has just been copied to the new workbook "book1") to the button, all by running code from the original workbook.
Thanks Jon

 

[Andrew Poulsom]

Try it with:

.OnAction = "Book1.xlsm!msg"

 

[JBRTaylor]

Thanks Andrew, that is unbelivably simple. Thanks also to everyone else for their help, it is always great to learn new code. I'll be booking myself onto a VBA course soon to learn the basics. Thanks all, no doubt i'll be back again soon.
Jon

 

[pgc01]

Hi Jon

I'm glad it's working now!

I cannot, however, reproduce the problem.

This is how I tested.

In the Module1 of the Copymod workbook I have:

Option Explicit
 
Sub Msg()
MsgBox "Message old"
End Sub

and in the code I posted I added:

With wkbSrc.VBProject.VBComponents("Module1").CodeModule
    s = .Lines(1, .CountOfLines)
[COLOR=red][B]  s = Replace(s, "old", "new")[/B][/COLOR]
End With

This way the Sub Msg in the new module displays "new", instead of "old".

After running the code I click on the button in "Book1" and get the right message, as it should be.

 

[Andrew Poulsom]

I tried it with:

Sub msg()
    MsgBox "Hello from " & ThisWorkbook.Name
End Sub

in Excel 2003 and got "Hello from CopyMod.xls" when I click the button in Book1.xls. That's why I suggested a change.