Copy a module from one workbook to another

With the macro below you can copy a module from one workbook to another:

Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As Workbook)
' requires a reference to the Microsoft Visual Basic Extensibility library
' copies a module from one workbook to another
' example: CopyModule Workbooks("Book1.xls"), "Module1", Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
    strFolder = SourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & ""
    strTempFile = strFolder & "~tmpexport.bas"
    On Error Resume Next
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0
End Sub

Document last updated 2008-04-26 11:22:47

User comments:

Ole P. from Norway wrote (2006-09-30 17:32:38 CET):

Re: Nothing happens !!
Hi!
Take a look at your macro settings: Tools, Macros, Security...
In the Security Level tab: make sure that the security level is set to Medium.
In the Trusted Publishers tab: check the option "Trust access to Visual Basic Project".

The macro "CopyModule" can not be run directly from the user interface in VBA or by using the menu/dialogs in Excel.
You will have to create another macro that requires no input to run the "CopyModule" procedure, e.g. like this:
Sub CopyMyModules()
CopyModule Workbooks("Book1.xls"), "Module1", Workbooks("Book2.xls")
End Sub

William from Singapore wrote (2006-09-30 11:16:02 CET):

Nothing happens !!
Hi Ole,

Sorry for jumping in here after this thread had stopped in May last year. I am desperately in need of this code to be operationable. I used WindowsXP, Excel 2003. I have referenced VB6.3 editor to "Microsoft Visual Basic for Applications Extensibility 5.3" in both Book1.xls and Book2.xls. I saved this code in standard Module 1 of Book1.xls, created a blank Module 1 in Book2.xls, saved it and kept both Book1.xls and Book2.xls open. I clicked run but the macro CopyModule is simply not in sight at all. Nothing happens as well. Where did I go wrong? please help!

Ole P. from Norway wrote (2005-04-25 14:31:24 CET):

Re: Nothing still happens...
Don't know what you are doing wrong.
The macro functioned perfectly in Excel 2000 when I created it, and it still works in Excel 2003 when I tested it today...
It will even work without the reference to the Microsoft Visual Basic Extensibility library.

Nicolas from Zürich (Switzerland) wrote (2005-04-22 09:20:47 CET):

Nothing still happens...
Hi Ole,

I've added the reference "Microsoft Visual Basic for Applications Extensibility 5.3".
I have saved the source workbook, I've opened both source and target workbooks and compiled & run the macro code you give.
And still nothing happens...

Sorry to bother you with such problems, but I really wonder where the problem comes from.

Thanks in advance for your help.

Ole P. from Norway wrote (2005-04-21 16:28:27 CET):

Re: Nothing happens !!!!!
You need a reference to the Microsoft Visual Basic Extensibility library (as stated in the macro example).
Both workbooks must be open and the source workbook must have been saved at least once (the workbook path is necessary).

Nicolas from Zürich (Switzerland) wrote (2005-04-21 11:17:09 CET):

Nothing happens !!!!!
Hi Ole,

I try to run the macro code you give to copy a module from one workbook to another, but nothing happens!!! The modules are not visible in the VBA Projet from the other workbook.
The macro seems to run (the computer runs!), but nothing else happens.

Must both workbooks be already closed for the copy operation ?
Should I load a special Library with special references ?

I met the same problem by running one of your other macro codes: "How to delete a module from a workbook". Nothing happened too: the module was and is still there!!?

Thanks in advance for all your ideas.

Ole P. from Norway wrote (2004-05-25 19:04:14 CET):

Re: What if the project is protected??
You have to either unprotect the project manually or use the SendKeys method in VBA (see the built-in help for details) to unprotect the project before exporting the module. This is however not a very good solution.
There is no built-in method in VBA that is suited to unprotect a VBProject.

jose wrote (2004-05-25 14:58:21 CET):

What if the project is protected??
I wan to export a module from a protected Project??
How do I un protect the Project before exporting the module??