Solved: VBA code to create / insert macro

[sai_golden]

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]

You could save the module in the other workbook, assumig it is open, then import that

[vba]

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

[mdmackillop]

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
[vba]
Workbooks("Book1").VBProject.VBComponents("Module1").Export "C:\AAA\myMod.txt"
[/vba]

[Bob Phillips]

As it used regulalry, best to setup a variable

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

Workbooks("Book1").VBProject.VBComponents("Module1").Export tempFile
ThisWorkbook.VBProject.VBComponents.Import tempFile
Kill tempFile
[/vba]

I definitely need to setup a Vista VM!

[sai_golden]

Hi All,

Thanks a lot.

Bob,

Thanks for the brilliant idea

Regards
Sai