Copy Code Module from One Workbook to Another

tcarden
NewLounger
Posts: 2
Joined: 23 Sep 2010, 13:06

Copy Code Module from One Workbook to Another

Post by tcarden »

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

User avatar
HansV
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

Post by HansV »

Welcome to Eileen's Lounge!

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
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.
Best wishes,
Hans

tcarden
NewLounger
Posts: 2
Joined: 23 Sep 2010, 13:06

Re: Copy Code Module from One Workbook to Another

Post by tcarden »

Thanks Hans. That looks to have worked.