Copy a code module from one workbook to another

[fuzzymonkey]

I need to copy a code module (say Module1) from the ActiveWorkBook into another workbook (say Book2) using VBA. I can do it in the editor, but am trying to automate this.

 

[Jon von der Heyden]

Hello

For example:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] CopyModule()
    [COLOR="Blue"]Const[/COLOR] strModName [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR] = "C:\TempMod.BAS"
    
    ActiveWorkbook.VBProject.VBComponents("MyModule").Export fielname:=strModName
    Workbooks(2).VBProject.VBComponents.Import Filename:=strmodnamme
    [COLOR="Blue"]Kill[/COLOR] strModName
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[fuzzymonkey]

Thanks Jon, that did the job.

 

[pgc01]

Hi

Another option is to copy directly the text from one module to the other. This avoids creating/deleting an auxilliary file.

Try:

Public Sub CopyModule()
Dim VBCodMod1 As Object, VBCodMod2 As Object
 
Set VBCodMod1 = Workbooks("Book1").VBProject.VBComponents("Module1").CodeModule
Set VBCodMod2 = Workbooks("Book2").VBProject.VBComponents.Add(1).CodeModule
VBCodMod2.DeleteLines 1, VBCodMod2.CountOfLines
VBCodMod2.AddFromString VBCodMod1.Lines(1, VBCodMod1.CountOfLines)
End Sub