MrExcel.com - Your One Stop for Excel Tips & Solutions

VBA code to export/import VBA Project Modules 97

Posted by Bruce Sommer on December 23, 1999 12:31 PM

In 95 the macros were worksheets and the below code worked: ( Where Update_Summary was the module name)

ThisWorkbook.Sheets("Update_Summary").Copy_ After:=ActiveWorkbook.Sheets(Sheets.Count)

In 97, how do you copy a VBA module from ThisWorkbook to ActiveWorkbook ?

I have tried:
Set exportmodule = ThisWorkbook.VBProject.VBComponents("Update_Summary").CodeModule

to capture the code of the module in the variable exportmodule..... run into a wall after that. on-line help is not very...

Posted by Tom Morales on December 27, 1999 3:16 PM

Bruce - A couple of thoughts. Make sure that you've set up your available VBA library to include the "Microsoft Visual Basic For Applications Extensibility" (Tools/References in the VBA editor.)

As for on-line help, there's a file on the Office 97 disc called VEENOB3.hlp which doesn't load with the overall Office 97. (Why make things easy?) That file provides guidance on playing with Visual Basic in macros. Load it onto your hard drive and, if you intend to use it frequently, put a short-cut to the file on your desktop.
Good luck
Tom

Posted by Ivan Moala on December 27, 1999 10:49 PM

Bruce
Tom is Correct as usuall.
For more info have a look @ Chip Pearson's
excellent site.
http://www.cpearson.com/excel/vbe.htm

Regards

Ivan


Posted by Bruce Sommer on December 29, 1999 9:35 AM

Thanks to all for the suggestions. I kept plugging away and found a solution using the export/import method of the VBComponents object(s)

seems like I had to activate the desired Module_Name first before I could export/import and then Kill the file to remove after the import. Let me know if there is a better approach.

Here is the code.

'Copy the required macros over to new workbook
ThisWorkbook.VBProject.VBComponents _("Module_Name").Activate
ThisWorkbook.VBProject.VBComponents _("Update_Summary").Export ("Module_Name")
ActiveWorkbook.VBProject.VBComponents.Import _("Module_Name")
Kill "Module_Name" ' Deletes macro file.