How do you import a module without adding a "1" at the module name?

[bubbapost]

Hello,

I have an addin that creates mulitple reports and since each of the report modules are updated by a different person, I have code that will delete the old module and import the new one for each one of the reports.

The process goes as follows:

1. Delete each of the old modules.
2. Import the new modules.
3. Here is where the issue is. When each of the new modules are imported, a "1" is added to the end and I end up having to run another macro "RenameModules" to rename the modules. I understand that a "1" would be added if I am importing another module with the same name.

Any ideas?

Is vba getting ahead of itself?

My code is below.

Public Const strMod2Loc = "C:\UpdatedModules\Mod2.bas"
Public Const strMod3Loc = "C:\UpdatedModules\Mod3.bas"
Public Const strMod4Loc = "C:\UpdatedModules\Mod4.bas"
Public Const strMod5Loc = "C:\UpdatedModules\Mod5.bas"
Public Const strMod6Loc = "C:\UpdatedModules\Mod6.bas"
Public Const strMod2Name = "Mod2"
Public Const strMod3Name = "Mod3"
Public Const strMod4Name = "Mod4"
Public Const strMod5Name = "Mod5"
Public Const strMod6Name = "Mod6"

Sub UpdateModules()

ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(strMod2Name)
ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(strMod3Name)
ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(strMod4Name)
ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(strMod4Name)
ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(strMod6Name)

InsertModule ThisWorkbook, strMod2Loc
InsertModule ThisWorkbook, strMod3Loc
InsertModule ThisWorkbook, strMod4Loc
InsertModule ThisWorkbook, strMod5Loc
InsertModule ThisWorkbook, strMod6Loc
End Sub

Sub RenameModules()
On Error Resume Next
ThisWorkbook.VBProject.VBComponents("Mod21").Name = "Mod2"
ThisWorkbook.VBProject.VBComponents("Mod31").Name = "Mod3"
ThisWorkbook.VBProject.VBComponents("Mod41").Name = "Mod4"
ThisWorkbook.VBProject.VBComponents("Mod51").Name = "Mod5"
ThisWorkbook.VBProject.VBComponents("Mod61").Name = "Mod6"
On Error GoTo 0
End Sub

 

[Domenic]

If a "1" is being added to the module name, it suggests that a module by that name already exists. Try stepping through the code, line by line, and see if each of the modules are in fact being deleted before the new ones are imported.

 

[bubbapost]

Domenic,

I have stepped through the code and each of the modules are being deleted prior to imorting the new modules.

Not sure what else I can do.

Andy

 

[Domenic]

Can you post the code you're using for "InsertModule"?

 

[Domenic]

I've got to get going for now, but if no one else jumps in to help, I'll look at it when I get a chance.

 

[bubbapost]

Here it is:

InsertModule ThisWorkbook, strMod2Loc
InsertModule ThisWorkbook, strMod3Loc
InsertModule ThisWorkbook, strMod4Loc
InsertModule ThisWorkbook, strMod5Loc
InsertModule ThisWorkbook, strMod6Loc

 

[Domenic]

It looks like each line is calling a sub called "InsertModule" and passing to it the workbook and a string. I wanted to see the actual code used for the sub "InsertModule". In any case, try the following...

[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Sub[/COLOR] UpdateModules()
    [COLOR=darkblue]Dim[/COLOR] MyPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ModuleNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    MyPath = "C:\UpdateModules\"
    
    [COLOR=darkblue]If[/COLOR] Right(MyPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] MyPath = MyPath & "\"
    
    ModuleNames = Array("Mod2", "Mod3", "Mod4", "Mod5", "Mod6")
    
    [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](ModuleNames) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](ModuleNames)
        [COLOR=darkblue]With[/COLOR] ThisWorkbook.VBProject.VBComponents
            .Remove .Item(ModuleNames(i))
            .Import MyPath & ModuleNames(i) & ".bas"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]

 

[bubbapost]

Sorry about that, here is the InsertModule code that I am using:

Sub InsertModule(ByVal wb As Workbook, ByVal CompFileName As String)
If Dir(CompFileName) <> "" Then
    On Error Resume Next ' ignores any errors if the project is protected
    wb.VBProject.VBComponents.Import CompFileName
    On Error GoTo 0
End If
Set wb = Nothing
End Sub

 

[bubbapost]

It looks like each line is calling a sub called "InsertModule" and passing to it the workbook and a string. I wanted to see the actual code used for the sub "InsertModule". In any case, try the following...

[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Sub[/COLOR] UpdateModules()
    [COLOR=darkblue]Dim[/COLOR] MyPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ModuleNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    MyPath = "C:\UpdateModules\"
    
    [COLOR=darkblue]If[/COLOR] Right(MyPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] MyPath = MyPath & "\"
    
    ModuleNames = Array("Mod2", "Mod3", "Mod4", "Mod5", "Mod6")
    
    [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](ModuleNames) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](ModuleNames)
        [COLOR=darkblue]With[/COLOR] ThisWorkbook.VBProject.VBComponents
            .Remove .Item(ModuleNames(i))
            .Import MyPath & ModuleNames(i) & ".bas"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]

Domenic,

This is great code however, when some of the modules are imported, a "1" is being added to the end of the module name.

Maybe I should have one proceedure that deletes the old modules, and another that imports the new modules. Running them both independantly of each other?

Thank you for offering your help, and for any future help you may provide.

Andy

 

[bubbapost]

It looks like each line is calling a sub called "InsertModule" and passing to it the workbook and a string. I wanted to see the actual code used for the sub "InsertModule". In any case, try the following...

[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
        [COLOR=darkblue]With[/COLOR] ThisWorkbook.VBProject.VBComponents
            .Remove .Item(ModuleNames(i))
            .Import MyPath & ModuleNames(i) & ".bas"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]

Domenic, after stepping through the code I am finding that the old module is not being removed prior to importing the new module. Not sure why.

Any ideas?

Andy