Import Modules
 

Hi,
I would like to create a macro to import
a Modules and a From another worksheet.

Thank you all

Import Modules
 

I'd start with Chip Pearson's site:
http://www.cpearson.com/excel/vbe.aspx

Alternatively, I'd create a workbook that had all the modules I
needed--including the userforms and save it as a template. Then I'd use that as
the basis for any new workbook.

(It may not work for what you're doing.)

Bre-x wrote:

Hi,
I would like to create a macro to import
a Modules and a From another worksheet.

Thank you all

--

Dave Peterson

Import Modules
 

The following code will copy the module named in ModuleName from
SourceWB to DestWB. You'll need a reference to the VBA Extensibility
library. In VBA, go to the Tools menu, choose References, and scroll
down to "Microsoft Visual Basic For Applications Extensiblity Library
5.3" and check that item. Change the lines marked with <<< to the
value appropriate for your circumstances.

Sub CopyModule

Dim TempName As String
Dim SourceWB As Workbook
Dim DestWB As Workbook
Dim ModuleName As String
Dim Ext As String
Dim VBComp As VBIDE.VBComponent
Dim Res As VbMsgBoxResult

Set SourceWB = Workbooks("Book3") '<<< CHANGE - Copy From
Set DestWB = Workbooks("Book2") '<<< CHANGE - Copy To
ModuleName = "SomeModule" '<<< CHANGE module to copy
On Error Resume Next
Set VBComp = SourceWB.VBProject.VBComponents(ModuleName)
If VBComp Is Nothing Then
MsgBox "The module '" & ModuleName & "' does not exist.", _
vbOKOnly
Exit Sub
End If
Err.Clear
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Ext = ".cls"
Case vbext_ct_MSForm
Ext = ".frm"
Case vbext_ct_StdModule
Ext = ".bas"
End Select

Set VBComp = Nothing
Set VBComp = DestWB.VBProject.VBComponents(ModuleName)
If Not VBComp Is Nothing Then
Res = MsgBox("The module '" & ModuleName & "' already exists." &
vbNewLine & _
"Do you want to replace it with the new version?", vbYesNo)
If Res = vbNo Then
Exit Sub
End If
VBComp.Collection.Remove VBComp
End If

TempName = Environ("temp") & "\" & ModuleName & Ext
If Dir(TempName, vbNormal) < vbNullString Then
Kill TempName
End If
SourceWB.VBProject.VBComponents(ModuleName).Export _
Filename:=TempName
DestWB.VBProject.VBComponents.Import _
Filename:=TempName

Kill TempName
End Sub

See www.cpearson.com/Excel/VBE.aspx for lots more information about
working with VBA objects and the extensibility library.

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




On Wed, 3 Feb 2010 12:24:24 -0700, "Bre-x"
wrote:

Hi,
I would like to create a macro to import
a Modules and a From another worksheet.

Thank you all

Import Modules
 

Hi Chip,

I was just wondering, are these steps really necessary???
Wouldn't it just be easier to copy the source code from
a source module and then paste into whichever module
I choose?? Just curious...

thankx


"Chip Pearson" wrote in message
...

The following code will copy the module named in ModuleName from
SourceWB to DestWB. You'll need a reference to the VBA Extensibility
library. In VBA, go to the Tools menu, choose References, and scroll
down to "Microsoft Visual Basic For Applications Extensiblity Library
5.3" and check that item. Change the lines marked with <<< to the
value appropriate for your circumstances.