PDA

View Full Version : Solved: VBA code to create / insert macro



sai_golden
06-01-2008, 10:23 AM
HI,

Is there any way by which an existing module in another excel book be copied / inserted in active workbook.

Please suggest if any.

Bob Phillips
06-01-2008, 11:27 AM
You could save the module in the other workbook, assumig it is open, then import that



Workbooks("Book1").VBProject.VBComponents("Module1").Export "C:\myMod.txt"
ThisWorkbook.VBProject.VBComponents.Import "C:\myMod.txt"
Kill "c:\myMod.txt"

mdmackillop
06-02-2008, 12:22 AM
I believe you cannot write a new file to the root directory in Vista, and in testing this code, this seems to be confirmed. The following adjustment works

Workbooks("Book1").VBProject.VBComponents("Module1").Export "C:\AAA\myMod.txt"

Bob Phillips
06-02-2008, 12:35 AM
As it used regulalry, best to setup a variable


Const tempFile as string = "C:\temp\myMod.txt"

Workbooks("Book1").VBProject.VBComponents("Module1").Export tempFile
ThisWorkbook.VBProject.VBComponents.Import tempFile
Kill tempFile


I definitely need to setup a Vista VM!

sai_golden
06-02-2008, 09:19 PM
Hi All,

Thanks a lot.

Bob,

Thanks for the brilliant idea:bow:

Regards
Sai