Does anybody know if it is possible to programmatically copy a code module from one Excel workbook to another workbook file that the executing code is generating dynamically?
I can see how to get a handle to a code module, as a VBComponent object, but there is no obvious way to copy this to the target workbook.
Thanks
Copy Code Module from One Workbook to Another
-
- NewLounger
- Posts: 2
- Joined: 23 Sep 2010, 13:06
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy Code Module from One Workbook to Another
Welcome to Eileen's Lounge!
One option is to export the module from the source workbook and to import it into the target workbook:
Another option would be to create a new module in the target workbook and to copy the lines from the source module into the new module.
One option is to export the module from the source workbook and to import it into the target workbook:
Code: Select all
Sub CopyModule()
' Just a random filename
Const strFileName = "C:\Temp\Temp1234.txt"
On Error Resume Next
' Delete file if it exists
Kill strFileName
On Error GoTo 0
' Export module from source workbook
Workbooks("Book1.xls").VBProject.VBComponents("Module1").Export Filename:=strFileName
' Import module into target workbook
Workbooks("Book2.xls").VBProject.VBComponents.Import Filename:=strFileName
' Delete the exported file
Kill strFileName
End Sub
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 23 Sep 2010, 13:06
Re: Copy Code Module from One Workbook to Another
Thanks Hans. That looks to have worked.