Removing & Importing a macro module
 

I have several macros in Module3. Several workbooks use the module. When i
make changes to the code, I copy the module to a folder for import into the
other workbooks. The "update" module resides in Module1. The problem is
this - when I select "Update Macro" (from a user menu) the Module 1 macros
run. It is supposed to delete Module3 and then Import the new module (file
name *.bas) that I have put in the current directory with the workbook.
However, it often does not delete Module3 but then adds Module31. When I
run Auto_Open I get and "Ambiuous" error message because Module3 is still
there. Here is the code I got from somebody in the newsgroup many months
ago.

How can I ensure that Module3 is deleted/removed and that the import is
named Module3 and not Module31?

==================
Sub UpdateCode()
FName = Dir("*.bas")

If Len(FName) = 0 Then
MsgBox "No Code files in the Directory"
Exit Sub
Else
Application.ScreenUpdating = False
Application.EnableEvents = False
Call DeleteModule
Call ImportModule (FName)
CodeFile = True
Call auto_open
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
If CodeFile Then
MsgBox "Code has been Updated"
Kill "*.bas"
Else
MsgBox "Code has NOT been updated - file not found"
End If

End Sub
Sub ImportModule(Modname As Variant)
ActiveWorkbook.VBProject.VBComponents.Import Modname
Application.Visible = True


End Sub
Sub DeleteModule()
Dim VBComp As VBComponent
On Error Resume Next
'remove module if named Module3 or Module31
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module3")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module31")
ThisWorkbook.VBProject.VBComponents.Remove VBComp

End Sub

Removing & Importing a macro module
 

It works okay for me, but I did notice one thing, you start by using
ActiveWorkbook,

Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module3")

and then switch to ThisWorkbook.

ThisWorkbook.VBProject.VBComponents.Remove VBComp

In mys test, they are the same, but probably not in your scenario. Seems
they should both be Activeworkbook to me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glen Mettler" wrote in message
...
I have several macros in Module3. Several workbooks use the module. When
i
make changes to the code, I copy the module to a folder for import into
the
other workbooks. The "update" module resides in Module1. The problem is
this - when I select "Update Macro" (from a user menu) the Module 1 macros
run. It is supposed to delete Module3 and then Import the new module
(file
name *.bas) that I have put in the current directory with the workbook.
However, it often does not delete Module3 but then adds Module31. When I
run Auto_Open I get and "Ambiuous" error message because Module3 is still
there. Here is the code I got from somebody in the newsgroup many months
ago.

How can I ensure that Module3 is deleted/removed and that the import is
named Module3 and not Module31?

==================
Sub UpdateCode()
FName = Dir("*.bas")

If Len(FName) = 0 Then
MsgBox "No Code files in the Directory"
Exit Sub
Else
Application.ScreenUpdating = False
Application.EnableEvents = False
Call DeleteModule
Call ImportModule (FName)
CodeFile = True
Call auto_open
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
If CodeFile Then
MsgBox "Code has been Updated"
Kill "*.bas"
Else
MsgBox "Code has NOT been updated - file not found"
End If

End Sub
Sub ImportModule(Modname As Variant)
ActiveWorkbook.VBProject.VBComponents.Import Modname
Application.Visible = True


End Sub
Sub DeleteModule()
Dim VBComp As VBComponent
On Error Resume Next
'remove module if named Module3 or Module31
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module3")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module31")
ThisWorkbook.VBProject.VBComponents.Remove VBComp

End Sub

Removing & Importing a macro module
 

Thanks Bob,
That was part of the problem. Now I am getting Module311 after deletes and
imports

Glen

"Bob Phillips" wrote in message
...
It works okay for me, but I did notice one thing, you start by using
ActiveWorkbook,

Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module3")

and then switch to ThisWorkbook.

ThisWorkbook.VBProject.VBComponents.Remove VBComp

In mys test, they are the same, but probably not in your scenario. Seems
they should both be Activeworkbook to me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glen Mettler" wrote in message
...
I have several macros in Module3. Several workbooks use the module.
When
i
make changes to the code, I copy the module to a folder for import into
the
other workbooks. The "update" module resides in Module1. The problem is
this - when I select "Update Macro" (from a user menu) the Module 1
macros
run. It is supposed to delete Module3 and then Import the new module
(file
name *.bas) that I have put in the current directory with the workbook.
However, it often does not delete Module3 but then adds Module31. When I
run Auto_Open I get and "Ambiuous" error message because Module3 is still
there. Here is the code I got from somebody in the newsgroup many months
ago.

How can I ensure that Module3 is deleted/removed and that the import is
named Module3 and not Module31?

==================
Sub UpdateCode()
FName = Dir("*.bas")

If Len(FName) = 0 Then
MsgBox "No Code files in the Directory"
Exit Sub
Else
Application.ScreenUpdating = False
Application.EnableEvents = False
Call DeleteModule
Call ImportModule (FName)
CodeFile = True
Call auto_open
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
If CodeFile Then
MsgBox "Code has been Updated"
Kill "*.bas"
Else
MsgBox "Code has NOT been updated - file not found"
End If

End Sub
Sub ImportModule(Modname As Variant)
ActiveWorkbook.VBProject.VBComponents.Import Modname
Application.Visible = True


End Sub
Sub DeleteModule()
Dim VBComp As VBComponent
On Error Resume Next
'remove module if named Module3 or Module31
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module3")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module31")
ThisWorkbook.VBProject.VBComponents.Remove VBComp

End Sub

Removing & Importing a macro module
 

I changed the macro slightly and now it works fine.

Glen

"Glen Mettler" wrote in message
...
I have several macros in Module3. Several workbooks use the module. When
i make changes to the code, I copy the module to a folder for import into
the other workbooks. The "update" module resides in Module1. The problem
is this - when I select "Update Macro" (from a user menu) the Module 1
macros run. It is supposed to delete Module3 and then Import the new
module (file name *.bas) that I have put in the current directory with the
workbook. However, it often does not delete Module3 but then adds Module31.
When I run Auto_Open I get and "Ambiuous" error message because Module3 is
still there. Here is the code I got from somebody in the newsgroup many
months ago.

How can I ensure that Module3 is deleted/removed and that the import is
named Module3 and not Module31?

==================
Sub UpdateCode()
FName = Dir("*.bas")

If Len(FName) = 0 Then
MsgBox "No Code files in the Directory"
Exit Sub
Else
Application.ScreenUpdating = False
Application.EnableEvents = False
Call DeleteModule
Call ImportModule (FName)
CodeFile = True
Call auto_open
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
If CodeFile Then
MsgBox "Code has been Updated"
Kill "*.bas"
Else
MsgBox "Code has NOT been updated - file not found"
End If

End Sub
Sub ImportModule(Modname As Variant)
ActiveWorkbook.VBProject.VBComponents.Import Modname
Application.Visible = True


End Sub
Sub DeleteModule()
Dim VBComp As VBComponent
On Error Resume Next
'remove module if named Module3 or Module31
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module3")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module31")
ThisWorkbook.VBProject.VBComponents.Remove VBComp

End Sub