Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Modules
Hi,
I would like to create a macro to import a Modules and a From another worksheet. Thank you all |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatic export/import code modules | Excel Programming | |||
Export Import Modules File Name error | Excel Programming | |||
Macro to import modules into a worksheet | Excel Discussion (Misc queries) | |||
VBA Code to Import VBA Modules | Excel Programming | |||
Need help on How to programatically Import VBA Project Excel Objects, Forms, Modules | Excel Programming |