Using VBA to remove a module

[mikechambers]

I have the following code in Workbook C to perform the following steps:

1. Export Module1 to a BAS file from Workbook A
2. Delete Module1 from Workbook B
3. Import BAS file into Workbook B as Module1

Code:

Dim FName As String, vbCom As Object
With Workbooks("Workbook A.xls")
FName = "C:\code.bas"
.VBProject.VBComponents("Module1").Export FName
End With
Set vbCom = Workbooks("Workbook B.xls").VBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Module1")
vbCom.Import FName
Kill FName

The problem is after the Remove line, Module1 still shows up in Workbook B, so that when it imports FName, it does so as Module11. BUT, if I end the macro after the remove line, Module1 disappears. So it seems like a refresh issue or something. It is not actually removed until the macro is finished running? Does that make sense? I tried Application.Screenupdating=True after the Remove line, but didn't fix it. Any ideas???

 

[RoryA]

Do you actually want that code to run, if you're changing the module?

 

[pgc01]

I have the following code in Workbook C to perform the following steps:

1. Export Module1 to a BAS file from Workbook A
2. Delete Module1 from Workbook B
3. Import BAS file into Workbook B as Module1

Hi Mike

If I understand correctly you just want to copy the code from Module1 in workbook A to WorkbookB.

I don't think you need to export/import, create a file, etc. Maybe it's easier to copy the code directly:

Public Sub CopyModuleText()
Dim VBCodMod1 As Object, VBCodMod2 As Object
 
Set VBCodMod1 = Workbooks("Workbook A.xls").VBProject.VBComponents("Module1").CodeModule
Set VBCodMod2 = Workbooks("Workbook B.xls").VBProject.VBComponents("Module1").CodeModule
 
VBCodMod2.DeleteLines 1, VBCodMod2.CountOfLines
VBCodMod2.AddFromString VBCodMod1.Lines(1, VBCodMod1.CountOfLines)
End Sub

 

[mikechambers]

Do you actually want that code to run, if you're changing the module?

No, I don't really need to run it. But is there a way to open a file, but not run the Open code?

 

[RoryA]

Yes - put:

application.enablevents = false

Before opening the workbooks. Make sure you set it back to true at the end of your routine.

 

[mikechambers]

I tried just copying the code per PGC's suggestion, but it caused Excel to crash. I think it's for the same reasoning. I guess since it is running the Open code from Module1, it then can't alter that code in the same procedure. So all I had to do was disable the Open routine from running, using rorya's reply, Application.EnableEvents=False and it works beautifully.

Thanks to everyone for the help. I really appreciate it!!!