VBA to Copy a Module to Another Workbook

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
115
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.


VBA Code:
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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
 
Last edited:
Solution

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
115
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.
VBA Code:
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

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Glad having been of some help (y). Sorry but have no experience with OneDrive, can't help.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,667
Messages
5,637,669
Members
416,979
Latest member
juliegeorge792

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top