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).Exp ort 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).Exp ort 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("Mod ule1").Export _
FILENAME:=FILENAME
On Error Resume Next
With Workbooks("Book2.xls").VBProject.VBComponents
.Remove .Item("Module1")
End With
Workbooks("Book2.xls").VBProject.VBComponents.Impo rt _
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
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).Exp ort 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).Exp ort 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).Exp ort 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("Mod ule1").Export _
FILENAME:=FILENAME
On Error Resume Next
With Workbooks("Book2.xls").VBProject.VBComponents
.Remove .Item("Module1")
End With
Workbooks("Book2.xls").VBProject.VBComponents.Impo rt _
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
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).Exp ort 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
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
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_Rep ort").Export Filename:=fPath &
"Mod_Print_Report.bas"

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


Gary Keramidas
Excel 2003


"Ken Hudson" wrote in message
...
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).Exp ort strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
End Sub
--
Ken Hudson