VBA to Copy a Module to Another Workbook

[sxhall]

Hi,

I've found some code on a few sites that will allow the copying of a module from one workbook to another. The workbook that it is being copied to is being created in another macro so will exist for the import part. I've played with the code for some time now and cannot get it to export the module so am guessing I'm missing something very obvious in the code that I have not yet seen!!!

What I need to be able to do is copy a module (Module1 in the below) form Book1 to Book2.

Sub CopyModule()

Dim strModuleName As String
Dim strFolder As String
Dim strTempFile As String

Workbooks("Book1").Activate

strFolder = Workbooks("Book1").Path

    If Len(strFolder) = 0 Then strFolder = CurDir

strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"

On Error Resume Next

    Workbooks("Book1").VBProject.VBComponents("Module1").Export strTempFile
    Workbooks("Book2").VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    
On Error GoTo 0
    
End Sub

For my test the above code has all files stored on the desktop, for ease.

Will then need to move over a user form as well so am hoping I can basically use the same code with reference to userforms instead of the module?

Thanks in advance for any assistance.


Steven

 

[rollis13]

I would use the file extension in the macro. I hope you are aware that the destination file has to be 'open'.
Yes for Userforms.

Option Explicit
Sub CopyModule()
    Dim strModuleName As String
    Dim strFolder As String
    Dim strTempFile As String
    Workbooks("Book1.xlsm").Activate              '<= changed
    strFolder = Workbooks("Book1.xlsm").Path      '<= changed
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    'On Error Resume Next                          '<= disabled for Debugging purpose
    Workbooks("Book1.xlsm").VBProject.VBComponents("Module1").Export strTempFile '<= changed
    Workbooks("Book2.xlsm").VBProject.VBComponents.Import strTempFile '<= changed
    Kill strTempFile
    On Error GoTo 0
End Sub

 

[sxhall]

I would use the file extension in the macro. I hope you are aware that the destination file has to be 'open'.
Yes for Userforms.

Option Explicit
Sub CopyModule()
    Dim strModuleName As String
    Dim strFolder As String
    Dim strTempFile As String
    Workbooks("Book1.xlsm").Activate              '<= changed
    strFolder = Workbooks("Book1.xlsm").Path      '<= changed
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    'On Error Resume Next                          '<= disabled for Debugging purpose
    Workbooks("Book1.xlsm").VBProject.VBComponents("Module1").Export strTempFile '<= changed
    Workbooks("Book2.xlsm").VBProject.VBComponents.Import strTempFile '<= changed
    Kill strTempFile
    On Error GoTo 0
End Sub

Thanks Rollis,

This worked . Will now attempt for the userform. Thankfully I knew about the file being open and in the full code I create the file so know it's name location etc.

For any future references for others reading I tried to save the module in to my one-drive and it did not seem to like this. Changed the file to a local file and it works.

Steven.

 

[rollis13]

Glad having been of some help . Sorry but have no experience with OneDrive, can't help.