Solved: Copy Module or UserForm into another file

[Philcjr]

All,

This is a two question post:

1) Is there a way to copy a Module (Formats) from from one file (Book1.xls or activeworkbook) to another file (Book2.xls)?

2) Is there a way to copy a UserForm (Data) from from one file (Book1.xls or activeworkbook) to another file (Book2.xls)?

Thanks for any insight on this,
Phil

[Amanda1]

Hi Phil

The only way that I know how to do it is via dragging the module and/or userform from original worksheet in VBE to the new worksheet that you want to put it into. (Obviously with both of them open otherwise they aren't open in the VBE - I can't think of another way to do it from within the applications - short of inserting new module/userform and copying from original modules into the new ones on the new worksheet. You can always do it from the bas files etc, but that can be a bit painful.

Hope it helps

Cheers

Amanda

[Philcjr]

Amanda,
Thanks for your insight, however, I was hoping there was a way to do this via some coding.

Phil

[Amanda1]

AhAH!

I've got no doubt there is, but I'm only starting to take baby steps on that - so I'm afraid I can't help you - sorry

Cheers

Amanda

[Zack Barresse]

Hey Phil,

This code is from Chip Pearson's site and exports all modules - all - modules, forms, class modules ..

[vba]Sub ExportAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim Sfx As String

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
End If
Next VBComp
End Sub[/vba]

And Johnske has some code here to browse and import what you'd like ..

http://vbaexpress.com/kb/getarticle.php?kb_id=397

You could of course, combine these if you'd like to make it one process by defining both workbooks and using the Import/Export method.

HTH

[BlueCactus]

I've got one sitting in the KB queue that allows importing / exporting to any open book. I wrote it for Mac originally because Mac VBE does not have the Import / Export commands, but it works with Windows too. If you'd like an 'advance copy', send me a PM.

BTW, if you incorporate this kind of code it is quite likely that you will upset your virus software.

[johnske]

Modify this to suit[vba]Option Explicit

Public ThisBook As Workbook, OtherBook As Workbook

Sub AmalgamateBooks()

Set ThisBook = ActiveWorkbook

'set reference to Microsoft Visual Basic
'for Applications Extensibility 5.3
On Error Resume Next
ThisBook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3

Dim ImportCode$, ImportAllCode$, i&, N&

ImportCode = MsgBox("Import the code modules from these workbooks?" & vbLf & _
"" & vbLf & _
"NOTE: Imported code may malfunction if there are" & vbLf & _
"references to worksheets included in the code...", vbYesNo)
If ImportCode = vbYes Then
ImportAllCode = MsgBox("Include all 'ThisWorkbook' " & _
"and Worksheet modules?" & vbLf & _
"" & vbLf & _
"NOTE: 'ThisWorkbook' and Sheet modules can only" & vbLf & _
"be imported as ''Class'' modules...", vbYesNo)
End If
If ImportCode = vbYes And ImportAllCode = vbYes Then
ImportCode = vbNo
End If
Application.ScreenUpdating = False
With Application.FileSearch
.LookIn = ActiveWorkbook.Path
.FileName = "*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) <> ThisWorkbook.FullName Then
Application.Workbooks.Open(.FoundFiles(i)).Activate
Set OtherBook = ActiveWorkbook
With OtherBook
For N = 1 To Sheets.Count
Sheets(N).Activate
Cells.Copy
With ThisBook
.Activate
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
[A1].Select
Application.CutCopyMode = False
End With
.Activate
Next N
If ImportCode = vbYes Then
TransferModules
ElseIf ImportAllCode = vbYes Then
TransferAllModules
End If
End With
With OtherBook
.Close False
End With
End If
Next i
End If
End With
Sheet1.Activate
End Sub

Private Sub TransferModules()
Dim Module$, Component As VBIDE.VBComponent
With OtherBook
Module = .Path & "\code.txt"
For Each Component In .VBProject.VBComponents
If Component.Type <> vbext_ct_Document Then
Component.Export Module
ThisBook.VBProject.VBComponents.Import Module
End If
Next Component
End With
End Sub

Private Sub TransferAllModules()
Dim Module$, Component As VBIDE.VBComponent
With OtherBook
Module = .Path & "\code.txt"
For Each Component In .VBProject.VBComponents
Component.Export Module
ThisBook.VBProject.VBComponents.Import Module
Next Component
End With
End Sub[/vba]

[Philcjr]

Johnske,

Very cool, this is a great start. THANK YOU!

Phil