How to import events VBA code in Excel?

[JYH]

Good day everyone,

I have a small fleet of Excel apps who, sometime, need updating.
For modules, I simply
ThisWorkbook.VBProject.VBComponents("WhateverModule").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)

But I can not use the same code to import into ThisWorkbook as I do
for modules.
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)

Instead of replacing (or adding) the code within ThisWorkbook, it
creates a new Class Modules named "ThisWorkbook1".

Do you have any suggestion on how could I fix that?

The only other solution is to manually set all my events to something
static refering to a module.

Thank you

 

[Per Jessen]

Hello,

Look at Chip Pearson's site:

http://www.cpearson.com/excel/vbe.aspx

The section "Copy A Module From One Project To Another" should solve
your problem.

Regards,
Per

 

[JYH]

Thank you very much.

I am not sure why my code wasn't working but I'll use his code until I
can figure out why.

Thank you again very much

 

[rahul_var]

Hello,
Recently I had been through same problem, which I could fix.
I was trying to remove the code module from existing worksheet and then trying to insert new module with same name as old code module. This code was written in XLA and was called from a sub routine in the old module.
The result was : old module was replaced with new module with suffix '1'.
The reason was:
The code written to do this task as part of the old module. So while removing old module, it was not actually removing it, since the code was locked. So the new module with suffix '1' was created.
As of code execution was over, the old module is removed.
To fix this...
I directly called the sub routine from button in the Worksheet and it worked. Since old module was not locked, it was removed and new module with same name was inserted.

I hope this would help.

Thanks,

Rahul Varadkar

Good day everyone,

I have a small fleet of Excel apps who, sometime, need updating.
For modules, I simply
ThisWorkbook.VBProject.VBComponents("WhateverModule").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)

But I can not use the same code to import into ThisWorkbook as I do
for modules.
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Export
tempFile
DestWbk.VBProject.VBComponents.Import tempFile
Kill (tempFile)

Instead of replacing (or adding) the code within ThisWorkbook, it
creates a new Class Modules named "ThisWorkbook1".

Do you have any suggestion on how could I fix that?

The only other solution is to manually set all my events to something
static refering to a module.

Thank you