I am using Excel 2003.
I have two open workbooks: one containing a macro and another workbook with 
no modules.
I want to copy Module1 from the macro workbook to the other open workbook.
I found some code that I tried to adapt but it is not working.
Can someone tell me how to fix it?
Do I need to add any specific references?

Sub CopyModule()
Dim strFolder As String
Dim strTempFile As String
Dim TargetWB As Workbook
Dim SourceWB As Workbook
Dim strModuleName As String
    strTempFile = "C:\test\" & "~tmpexport.bas"
    Set SourceWB = Workbooks("Book1")
    Set TargetWB = Workbooks("Book2")
    strModuleName = "Module1.bas"
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
End Sub
-- 
Ken Hudson

you should be able to find something here to help you.

http://www.cpearson.com/excel/vbe.aspx
-- 
HTH,

Barb Reinhardt



"Ken Hudson" wrote:

> I am using Excel 2003.
> I have two open workbooks: one containing a macro and another workbook with 
> no modules.
> I want to copy Module1 from the macro workbook to the other open workbook.
> I found some code that I tried to adapt but it is not working.
> Can someone tell me how to fix it?
> Do I need to add any specific references?
> 
> Sub CopyModule()
> Dim strFolder As String
> Dim strTempFile As String
> Dim TargetWB As Workbook
> Dim SourceWB As Workbook
> Dim strModuleName As String
>     strTempFile = "C:\test\" & "~tmpexport.bas"
>     Set SourceWB = Workbooks("Book1")
>     Set TargetWB = Workbooks("Book2")
>     strModuleName = "Module1.bas"
>     SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
>     TargetWB.VBProject.VBComponents.Import strTempFile
>     Kill strTempFile
> End Sub
> -- 
> Ken Hudson

Try code like

Const FILENAME = "C:\Temp\Module1.bas"
On Error Resume Next
MkDir "C:\Temp"
Kill FILENAME
On Error GoTo 0
Workbooks("Book1.xls").VBProject.VBComponents("Module1").Export _
    FILENAME:=FILENAME
On Error Resume Next
With Workbooks("Book2.xls").VBProject.VBComponents
    .Remove .Item("Module1")
End With
Workbooks("Book2.xls").VBProject.VBComponents.Import _
    FILENAME:=FILENAME
Kill FILENAME


See also www.cpearson.com/excel/vbe.aspx for lots more code about
manipulating the VBA editor and code using code.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]







On Sat, 19 Dec 2009 16:46:02 -0800, Ken Hudson
<KenHudson@discussions.microsoft.com> wrote:

>I am using Excel 2003.
>I have two open workbooks: one containing a macro and another workbook with 
>no modules.
>I want to copy Module1 from the macro workbook to the other open workbook.
>I found some code that I tried to adapt but it is not working.
>Can someone tell me how to fix it?
>Do I need to add any specific references?
>
>Sub CopyModule()
>Dim strFolder As String
>Dim strTempFile As String
>Dim TargetWB As Workbook
>Dim SourceWB As Workbook
>Dim strModuleName As String
>    strTempFile = "C:\test\" & "~tmpexport.bas"
>    Set SourceWB = Workbooks("Book1")
>    Set TargetWB = Workbooks("Book2")
>    strModuleName = "Module1.bas"
>    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
>    TargetWB.VBProject.VBComponents.Import strTempFile
>    Kill strTempFile
>End Sub

Thanks for the link Barb.
I had seen Chip's code earlier but calling functions and sending arguments 
are a bit too difficult for me at my knowledge level. 

I got my original code to work as follows:

Sub CopyModule()
Dim strFolder As String
Dim strTempFile As String
Dim TargetWB As Workbook
Dim SourceWB As Workbook
Dim strModuleName As String
    strTempFile = "C:\test\" & "~tmpexport.bas"
    Set SourceWB = Workbooks("Book1")
    Set TargetWB = Workbooks("Book2")
    strModuleName = "Module1"
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
End Sub

-- 
Ken Hudson


"Barb Reinhardt" wrote:

> you should be able to find something here to help you.
> 
> http://www.cpearson.com/excel/vbe.aspx
> -- 
> HTH,
> 
> Barb Reinhardt
> 
> 
> 
> "Ken Hudson" wrote:
> 
> > I am using Excel 2003.
> > I have two open workbooks: one containing a macro and another workbook with 
> > no modules.
> > I want to copy Module1 from the macro workbook to the other open workbook.
> > I found some code that I tried to adapt but it is not working.
> > Can someone tell me how to fix it?
> > Do I need to add any specific references?
> > 
> > Sub CopyModule()
> > Dim strFolder As String
> > Dim strTempFile As String
> > Dim TargetWB As Workbook
> > Dim SourceWB As Workbook
> > Dim strModuleName As String
> >     strTempFile = "C:\test\" & "~tmpexport.bas"
> >     Set SourceWB = Workbooks("Book1")
> >     Set TargetWB = Workbooks("Book2")
> >     strModuleName = "Module1.bas"
> >     SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
> >     TargetWB.VBProject.VBComponents.Import strTempFile
> >     Kill strTempFile
> > End Sub
> > -- 
> > Ken Hudson

Hi Chip,
I feel kinda blessed to get a response from you....
To plain vanilla programmers like me, it is like getting a note from Santa.
Thanks for the tip.

I would really like to use the code from your website as shown below. When I 
do, I get an "object required" error on the function call line of code.

Can you tell me what is wrong with the code?

And a second question: If I have a macro shortcut associated with the copied 
module, will that be included? If not, is there code I can use to make that 
happen?

Warmest regards.....


Sub test()
    Call CopyModule("Module2", Sunday.xls, Sunday2.xls, True)
End Sub

--------------------------------------------------------------------------------

Function CopyModule(ModuleName As String, _
    FromVBProject As VBIDE.VBProject, _
    ToVBProject As VBIDE.VBProject, _
    OverwriteExisting As Boolean) As Boolean
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' CopyModule
    ' This function copies a module from one VBProject to
    ' another. It returns True if successful or  False
    ' if an error occurs.
    '
    ' Parameters:
    ' --------------------------------
    ' FromVBProject         The VBProject that contains the module
    '                       to be copied.
    '
    ' ToVBProject           The VBProject into which the module is
    '                       to be copied.
    '
    ' ModuleName            The name of the module to copy.
    '
    ' OverwriteExisting     If True, the VBComponent named ModuleName
    '                       in ToVBProject will be removed before
    '                       importing the module. If False and
    '                       a VBComponent named ModuleName exists
    '                       in ToVBProject, the code will return
    '                       False.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim VBComp As VBIDE.VBComponent
    Dim FName As String
    Dim CompName As String
    Dim S As String
    Dim SlashPos As Long
    Dim ExtPos As Long
    Dim TempVBComp As VBIDE.VBComponent
    
    '''''''''''''''''''''''''''''''''''''''''''''
    ' Do some housekeeping validation.
    '''''''''''''''''''''''''''''''''''''''''''''
    If FromVBProject Is Nothing Then
        CopyModule = False
        Exit Function
    End If
    
    If Trim(ModuleName) = vbNullString Then
        CopyModule = False
        Exit Function
    End If
    
    If ToVBProject Is Nothing Then
        CopyModule = False
        Exit Function
    End If
    
    If FromVBProject.Protection = vbext_pp_locked Then
        CopyModule = False
        Exit Function
    End If
    
    If ToVBProject.Protection = vbext_pp_locked Then
        CopyModule = False
        Exit Function
    End If
    
    On Error Resume Next
    Set VBComp = FromVBProject.VBComponents(ModuleName)
    If Err.Number <> 0 Then
        CopyModule = False
        Exit Function
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' FName is the name of the temporary file to be
    ' used in the Export/Import code.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    FName = Environ("Temp") & "\" & ModuleName & ".bas"
    If OverwriteExisting = True Then
        ''''''''''''''''''''''''''''''''''''''
        ' If OverwriteExisting is True, Kill
        ' the existing temp file and remove
        ' the existing VBComponent from the
        ' ToVBProject.
        ''''''''''''''''''''''''''''''''''''''
        If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
            Err.Clear
            Kill FName
            If Err.Number <> 0 Then
                CopyModule = False
                Exit Function
            End If
        End If
        With ToVBProject.VBComponents
            .Remove .Item(ModuleName)
        End With
    Else
        '''''''''''''''''''''''''''''''''''''''''
        ' OverwriteExisting is False. If there is
        ' already a VBComponent named ModuleName,
        ' exit with a return code of False.
        ''''''''''''''''''''''''''''''''''''''''''
        Err.Clear
        Set VBComp = ToVBProject.VBComponents(ModuleName)
        If Err.Number <> 0 Then
            If Err.Number = 9 Then
                ' module doesn't exist. ignore error.
            Else
                ' other error. get out with return value of False
                CopyModule = False
                Exit Function
            End If
        End If
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Do the Export and Import operation using FName
    ' and then Kill FName.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    FromVBProject.VBComponents(ModuleName).Export Filename:=FName
    
    '''''''''''''''''''''''''''''''''''''
    ' Extract the module name from the
    ' export file name.
    '''''''''''''''''''''''''''''''''''''
    SlashPos = InStrRev(FName, "\")
    ExtPos = InStrRev(FName, ".")
    CompName = Mid(FName, SlashPos + 1, ExtPos - SlashPos - 1)
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Document modules (SheetX and ThisWorkbook)
    ' cannot be removed. So, if we are working with
    ' a document object, delete all code in that
    ' component and add the lines of FName
    ' back in to the module.
    ''''''''''''''''''''''''''''''''''''''''''''''
    Set VBComp = Nothing
    Set VBComp = ToVBProject.VBComponents(CompName)
    
    If VBComp Is Nothing Then
        ToVBProject.VBComponents.Import Filename:=FName
    Else
        If VBComp.Type = vbext_ct_Document Then
            ' VBComp is destination module
            Set TempVBComp = ToVBProject.VBComponents.Import(FName)
            ' TempVBComp is source module
            With VBComp.CodeModule
                .DeleteLines 1, .CountOfLines
                S = TempVBComp.CodeModule.Lines(1, 
TempVBComp.CodeModule.CountOfLines)
                .InsertLines 1, S
            End With
            On Error GoTo 0
            ToVBProject.VBComponents.Remove TempVBComp
        End If
    End If
    Kill FName
    CopyModule = True
End Function


-- 
Ken Hudson


"Chip Pearson" wrote:

> Try code like
> 
> Const FILENAME = "C:\Temp\Module1.bas"
> On Error Resume Next
> MkDir "C:\Temp"
> Kill FILENAME
> On Error GoTo 0
> Workbooks("Book1.xls").VBProject.VBComponents("Module1").Export _
>     FILENAME:=FILENAME
> On Error Resume Next
> With Workbooks("Book2.xls").VBProject.VBComponents
>     .Remove .Item("Module1")
> End With
> Workbooks("Book2.xls").VBProject.VBComponents.Import _
>     FILENAME:=FILENAME
> Kill FILENAME
> 
> 
> See also www.cpearson.com/excel/vbe.aspx for lots more code about
> manipulating the VBA editor and code using code.
> 
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
> 
> 
> 
> 
> 
> 
> 
> On Sat, 19 Dec 2009 16:46:02 -0800, Ken Hudson
> <KenHudson@discussions.microsoft.com> wrote:
> 
> >I am using Excel 2003.
> >I have two open workbooks: one containing a macro and another workbook with 
> >no modules.
> >I want to copy Module1 from the macro workbook to the other open workbook.
> >I found some code that I tried to adapt but it is not working.
> >Can someone tell me how to fix it?
> >Do I need to add any specific references?
> >
> >Sub CopyModule()
> >Dim strFolder As String
> >Dim strTempFile As String
> >Dim TargetWB As Workbook
> >Dim SourceWB As Workbook
> >Dim strModuleName As String
> >    strTempFile = "C:\test\" & "~tmpexport.bas"
> >    Set SourceWB = Workbooks("Book1")
> >    Set TargetWB = Workbooks("Book2")
> >    strModuleName = "Module1.bas"
> >    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
> >    TargetWB.VBProject.VBComponents.Import strTempFile
> >    Kill strTempFile
> >End Sub
> .
> 

The parameters you are passing to the CopyModule function are not
correct. Use something like

CopyModule "Module1", Workbooks("FromWorkbook.xls").VBProject, _
	Workbooks("ToWorkbook.xls").VBProject, True

The second and third parameters are of the data type VBProject, which
represents all things VBA within a workbook. The VBProject contains,
among other things, all the modules within a workbook.

Note that you will need a reference to the Extensibility library. With
the workbook that contains the code open in VBA, go to the Tools menu,
choose Options, and scroll down to "Microsoft Visual Basic For
Applications Extensibility Library 5.3" and check that item.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]


On Sun, 20 Dec 2009 11:25:01 -0800, Ken Hudson
<KenHudson@discussions.microsoft.com> wrote:

>Hi Chip,
>I feel kinda blessed to get a response from you....
>To plain vanilla programmers like me, it is like getting a note from Santa.
>Thanks for the tip.
>
>I would really like to use the code from your website as shown below. When I 
>do, I get an "object required" error on the function call line of code.
>
>Can you tell me what is wrong with the code?
>
>And a second question: If I have a macro shortcut associated with the copied 
>module, will that be included? If not, is there code I can use to make that 
>happen?
>
>Warmest regards.....
>
>
>Sub test()
>    Call CopyModule("Module2", Sunday.xls, Sunday2.xls, True)
>End Sub
>
>--------------------------------------------------------------------------------
>
>Function CopyModule(ModuleName As String, _
>    FromVBProject As VBIDE.VBProject, _
>    ToVBProject As VBIDE.VBProject, _
>    OverwriteExisting As Boolean) As Boolean
>    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>    ' CopyModule
>    ' This function copies a module from one VBProject to
>    ' another. It returns True if successful or  False
>    ' if an error occurs.
>    '
>    ' Parameters:
>    ' --------------------------------
>    ' FromVBProject         The VBProject that contains the module
>    '                       to be copied.
>    '
>    ' ToVBProject           The VBProject into which the module is
>    '                       to be copied.
>    '
>    ' ModuleName            The name of the module to copy.
>    '
>    ' OverwriteExisting     If True, the VBComponent named ModuleName
>    '                       in ToVBProject will be removed before
>    '                       importing the module. If False and
>    '                       a VBComponent named ModuleName exists
>    '                       in ToVBProject, the code will return
>    '                       False.
>    '
>    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>    
>    Dim VBComp As VBIDE.VBComponent
>    Dim FName As String
>    Dim CompName As String
>    Dim S As String
>    Dim SlashPos As Long
>    Dim ExtPos As Long
>    Dim TempVBComp As VBIDE.VBComponent
>    
>    '''''''''''''''''''''''''''''''''''''''''''''
>    ' Do some housekeeping validation.
>    '''''''''''''''''''''''''''''''''''''''''''''
>    If FromVBProject Is Nothing Then
>        CopyModule = False
>        Exit Function
>    End If
>    
>    If Trim(ModuleName) = vbNullString Then
>        CopyModule = False
>        Exit Function
>    End If
>    
>    If ToVBProject Is Nothing Then
>        CopyModule = False
>        Exit Function
>    End If
>    
>    If FromVBProject.Protection = vbext_pp_locked Then
>        CopyModule = False
>        Exit Function
>    End If
>    
>    If ToVBProject.Protection = vbext_pp_locked Then
>        CopyModule = False
>        Exit Function
>    End If
>    
>    On Error Resume Next
>    Set VBComp = FromVBProject.VBComponents(ModuleName)
>    If Err.Number <> 0 Then
>        CopyModule = False
>        Exit Function
>    End If
>    
>    ''''''''''''''''''''''''''''''''''''''''''''''''''''
>    ' FName is the name of the temporary file to be
>    ' used in the Export/Import code.
>    ''''''''''''''''''''''''''''''''''''''''''''''''''''
>    FName = Environ("Temp") & "\" & ModuleName & ".bas"
>    If OverwriteExisting = True Then
>        ''''''''''''''''''''''''''''''''''''''
>        ' If OverwriteExisting is True, Kill
>        ' the existing temp file and remove
>        ' the existing VBComponent from the
>        ' ToVBProject.
>        ''''''''''''''''''''''''''''''''''''''
>        If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
>            Err.Clear
>            Kill FName
>            If Err.Number <> 0 Then
>                CopyModule = False
>                Exit Function
>            End If
>        End If
>        With ToVBProject.VBComponents
>            .Remove .Item(ModuleName)
>        End With
>    Else
>        '''''''''''''''''''''''''''''''''''''''''
>        ' OverwriteExisting is False. If there is
>        ' already a VBComponent named ModuleName,
>        ' exit with a return code of False.
>        ''''''''''''''''''''''''''''''''''''''''''
>        Err.Clear
>        Set VBComp = ToVBProject.VBComponents(ModuleName)
>        If Err.Number <> 0 Then
>            If Err.Number = 9 Then
>                ' module doesn't exist. ignore error.
>            Else
>                ' other error. get out with return value of False
>                CopyModule = False
>                Exit Function
>            End If
>        End If
>    End If
>    
>    ''''''''''''''''''''''''''''''''''''''''''''''''''''
>    ' Do the Export and Import operation using FName
>    ' and then Kill FName.
>    ''''''''''''''''''''''''''''''''''''''''''''''''''''
>    FromVBProject.VBComponents(ModuleName).Export Filename:=FName
>    
>    '''''''''''''''''''''''''''''''''''''
>    ' Extract the module name from the
>    ' export file name.
>    '''''''''''''''''''''''''''''''''''''
>    SlashPos = InStrRev(FName, "\")
>    ExtPos = InStrRev(FName, ".")
>    CompName = Mid(FName, SlashPos + 1, ExtPos - SlashPos - 1)
>    
>    ''''''''''''''''''''''''''''''''''''''''''''''
>    ' Document modules (SheetX and ThisWorkbook)
>    ' cannot be removed. So, if we are working with
>    ' a document object, delete all code in that
>    ' component and add the lines of FName
>    ' back in to the module.
>    ''''''''''''''''''''''''''''''''''''''''''''''
>    Set VBComp = Nothing
>    Set VBComp = ToVBProject.VBComponents(CompName)
>    
>    If VBComp Is Nothing Then
>        ToVBProject.VBComponents.Import Filename:=FName
>    Else
>        If VBComp.Type = vbext_ct_Document Then
>            ' VBComp is destination module
>            Set TempVBComp = ToVBProject.VBComponents.Import(FName)
>            ' TempVBComp is source module
>            With VBComp.CodeModule
>                .DeleteLines 1, .CountOfLines
>                S = TempVBComp.CodeModule.Lines(1, 
>TempVBComp.CodeModule.CountOfLines)
>                .InsertLines 1, S
>            End With
>            On Error GoTo 0
>            ToVBProject.VBComponents.Remove TempVBComp
>        End If
>    End If
>    Kill FName
>    CopyModule = True
>End Function

Thanks!
Hopefully I learned a little more about VB programming through this post.
I did have the new reference checked, following the note from your website.
(I see that the copy porcess did bring the macro shortcut with it also.)
Merry Christmas.
-- 
Ken Hudson


"Chip Pearson" wrote:

> The parameters you are passing to the CopyModule function are not
> correct. Use something like
> 
> CopyModule "Module1", Workbooks("FromWorkbook.xls").VBProject, _
> 	Workbooks("ToWorkbook.xls").VBProject, True
> 
> The second and third parameters are of the data type VBProject, which
> represents all things VBA within a workbook. The VBProject contains,
> among other things, all the modules within a workbook.
> 
> Note that you will need a reference to the Extensibility library. With
> the workbook that contains the code open in VBA, go to the Tools menu,
> choose Options, and scroll down to "Microsoft Visual Basic For
> Applications Extensibility Library 5.3" and check that item.
> 
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
> 
> 
> On Sun, 20 Dec 2009 11:25:01 -0800, Ken Hudson
> <KenHudson@discussions.microsoft.com> wrote:
> 
> >Hi Chip,
> >I feel kinda blessed to get a response from you....
> >To plain vanilla programmers like me, it is like getting a note from Santa.
> >Thanks for the tip.
> >
> >I would really like to use the code from your website as shown below. When I 
> >do, I get an "object required" error on the function call line of code.
> >
> >Can you tell me what is wrong with the code?
> >
> >And a second question: If I have a macro shortcut associated with the copied 
> >module, will that be included? If not, is there code I can use to make that 
> >happen?
> >
> >Warmest regards.....
> >
> >
> >Sub test()
> >    Call CopyModule("Module2", Sunday.xls, Sunday2.xls, True)
> >End Sub
> >
> >--------------------------------------------------------------------------------
> >
> >Function CopyModule(ModuleName As String, _
> >    FromVBProject As VBIDE.VBProject, _
> >    ToVBProject As VBIDE.VBProject, _
> >    OverwriteExisting As Boolean) As Boolean
> >    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> >    ' CopyModule
> >    ' This function copies a module from one VBProject to
> >    ' another. It returns True if successful or  False
> >    ' if an error occurs.
> >    '
> >    ' Parameters:
> >    ' --------------------------------
> >    ' FromVBProject         The VBProject that contains the module
> >    '                       to be copied.
> >    '
> >    ' ToVBProject           The VBProject into which the module is
> >    '                       to be copied.
> >    '
> >    ' ModuleName            The name of the module to copy.
> >    '
> >    ' OverwriteExisting     If True, the VBComponent named ModuleName
> >    '                       in ToVBProject will be removed before
> >    '                       importing the module. If False and
> >    '                       a VBComponent named ModuleName exists
> >    '                       in ToVBProject, the code will return
> >    '                       False.
> >    '
> >    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> >    
> >    Dim VBComp As VBIDE.VBComponent
> >    Dim FName As String
> >    Dim CompName As String
> >    Dim S As String
> >    Dim SlashPos As Long
> >    Dim ExtPos As Long
> >    Dim TempVBComp As VBIDE.VBComponent
> >    
> >    '''''''''''''''''''''''''''''''''''''''''''''
> >    ' Do some housekeeping validation.
> >    '''''''''''''''''''''''''''''''''''''''''''''
> >    If FromVBProject Is Nothing Then
> >        CopyModule = False
> >        Exit Function
> >    End If
> >    
> >    If Trim(ModuleName) = vbNullString Then
> >        CopyModule = False
> >        Exit Function
> >    End If
> >    
> >    If ToVBProject Is Nothing Then
> >        CopyModule = False
> >        Exit Function
> >    End If
> >    
> >    If FromVBProject.Protection = vbext_pp_locked Then
> >        CopyModule = False
> >        Exit Function
> >    End If
> >    
> >    If ToVBProject.Protection = vbext_pp_locked Then
> >        CopyModule = False
> >        Exit Function
> >    End If
> >    
> >    On Error Resume Next
> >    Set VBComp = FromVBProject.VBComponents(ModuleName)
> >    If Err.Number <> 0 Then
> >        CopyModule = False
> >        Exit Function
> >    End If
> >    
> >    ''''''''''''''''''''''''''''''''''''''''''''''''''''
> >    ' FName is the name of the temporary file to be
> >    ' used in the Export/Import code.
> >    ''''''''''''''''''''''''''''''''''''''''''''''''''''
> >    FName = Environ("Temp") & "\" & ModuleName & ".bas"
> >    If OverwriteExisting = True Then
> >        ''''''''''''''''''''''''''''''''''''''
> >        ' If OverwriteExisting is True, Kill
> >        ' the existing temp file and remove
> >        ' the existing VBComponent from the
> >        ' ToVBProject.
> >        ''''''''''''''''''''''''''''''''''''''
> >        If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
> >            Err.Clear
> >            Kill FName
> >            If Err.Number <> 0 Then
> >                CopyModule = False
> >                Exit Function
> >            End If
> >        End If
> >        With ToVBProject.VBComponents
> >            .Remove .Item(ModuleName)
> >        End With
> >    Else
> >        '''''''''''''''''''''''''''''''''''''''''
> >        ' OverwriteExisting is False. If there is
> >        ' already a VBComponent named ModuleName,
> >        ' exit with a return code of False.
> >        ''''''''''''''''''''''''''''''''''''''''''
> >        Err.Clear
> >        Set VBComp = ToVBProject.VBComponents(ModuleName)
> >        If Err.Number <> 0 Then
> >            If Err.Number = 9 Then
> >                ' module doesn't exist. ignore error.
> >            Else
> >                ' other error. get out with return value of False
> >                CopyModule = False
> >                Exit Function
> >            End If
> >        End If
> >    End If
> >    
> >    ''''''''''''''''''''''''''''''''''''''''''''''''''''
> >    ' Do the Export and Import operation using FName
> >    ' and then Kill FName.
> >    ''''''''''''''''''''''''''''''''''''''''''''''''''''
> >    FromVBProject.VBComponents(ModuleName).Export Filename:=FName
> >    
> >    '''''''''''''''''''''''''''''''''''''
> >    ' Extract the module name from the
> >    ' export file name.
> >    '''''''''''''''''''''''''''''''''''''
> >    SlashPos = InStrRev(FName, "\")
> >    ExtPos = InStrRev(FName, ".")
> >    CompName = Mid(FName, SlashPos + 1, ExtPos - SlashPos - 1)
> >    
> >    ''''''''''''''''''''''''''''''''''''''''''''''
> >    ' Document modules (SheetX and ThisWorkbook)
> >    ' cannot be removed. So, if we are working with
> >    ' a document object, delete all code in that
> >    ' component and add the lines of FName
> >    ' back in to the module.
> >    ''''''''''''''''''''''''''''''''''''''''''''''
> >    Set VBComp = Nothing
> >    Set VBComp = ToVBProject.VBComponents(CompName)
> >    
> >    If VBComp Is Nothing Then
> >        ToVBProject.VBComponents.Import Filename:=FName
> >    Else
> >        If VBComp.Type = vbext_ct_Document Then
> >            ' VBComp is destination module
> >            Set TempVBComp = ToVBProject.VBComponents.Import(FName)
> >            ' TempVBComp is source module
> >            With VBComp.CodeModule
> >                .DeleteLines 1, .CountOfLines
> >                S = TempVBComp.CodeModule.Lines(1, 
> >TempVBComp.CodeModule.CountOfLines)
> >                .InsertLines 1, S
> >            End With
> >            On Error GoTo 0
> >            ToVBProject.VBComponents.Remove TempVBComp
> >        End If
> >    End If
> >    Kill FName
> >    CopyModule = True
> >End Function
> .
> 

sometimes i just export and import the module, using something like this:

ThisWorkbook.VBProject.VBComponents("Mod_Print_Report").Export Filename:=fPath & 
"Mod_Print_Report.bas"

ActiveWorkbook.VBProject.VBComponents.Import Filename:=fPath & 
"Mod_Print_Report.bas"
-- 


Gary Keramidas
Excel 2003


"Ken Hudson" <KenHudson@discussions.microsoft.com> wrote in message 
news:E04D3B8F-39A1-4595-9FDD-30109AA2B2B3@microsoft.com...
>I am using Excel 2003.
> I have two open workbooks: one containing a macro and another workbook with
> no modules.
> I want to copy Module1 from the macro workbook to the other open workbook.
> I found some code that I tried to adapt but it is not working.
> Can someone tell me how to fix it?
> Do I need to add any specific references?
>
> Sub CopyModule()
> Dim strFolder As String
> Dim strTempFile As String
> Dim TargetWB As Workbook
> Dim SourceWB As Workbook
> Dim strModuleName As String
>    strTempFile = "C:\test\" & "~tmpexport.bas"
>    Set SourceWB = Workbooks("Book1")
>    Set TargetWB = Workbooks("Book2")
>    strModuleName = "Module1.bas"
>    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
>    TargetWB.VBProject.VBComponents.Import strTempFile
>    Kill strTempFile
> End Sub
> -- 
> Ken Hudson