Problem removing a VBA Module 1

[Lightning]

Hopefully someone can answer this problem for me.

I am trying to update a code module in Excel using the following code

Sub ReplaceModules()
'   Export Module from this workbook
    strFileName2 = ThisWorkbook.PATH & "\tempmodxxx.bas"
    ThisWorkbook.VBProject.VBComponents("PublicSubprocedures").Export strFileName2
      
'   Replace Module in UserBook
    Set VBP = ActiveWorkbook.VBProject
    Debug.Print ActiveWorkbook.Name
    On Error GoTo ErrHandle
    With VBP.VBComponents
        [COLOR=red][b].Remove VBP.VBComponents("PublicSubprocedures")[/b][/color]

        ActiveWorkbook.Save
        .Import strFileName2
    End With
    Application.EnableEvents = False
    ActiveWorkbook.Close savechanges:=True
    Application.EnableEvents = True
    
'   Delete the temorary module file
    Kill strFileName2
'    Workbooks(strFileName).Close savechanges:=True

    MsgBox "The module has been replaced.", vbInformation
    Exit Sub

ErrHandle:
'   Did an error occur?
        MsgBox Err.Number & ":" & Err.Description, vbCritical            '"ERROR. The module may not have been replaced.",

My problem is with the highlighted line of code. It doesn't generate an error, it simply doesn't do anything!! Everything else in the procedure executes correctly, but the old module is not removed. This of course then creates errors with duplicate procedures in the file.

I have already checked that the module name is spelled exactly the same as the original.

Can anyone suggest what the problem might be?


Cheers
Lightning

 

[combo]

You could try:
[tt].Remove VBP.VBComponents.Item("PublicSubprocedures")[/tt]
An alternative way of upgrading module was discussed a couple of years ago: thread707-614467

combo

 

[Lightning]

Combo
Thanks, but adding in the .Item didn't make any difference.

But, a star for you for the link to the previous thread. I had searched for anything previous, but obviously didn't get the keywords right.

Thanks again

Lightning

 

[TonyJollans]

When you say it does nothing, have you stepped through the code and seen that it does nothing? Or have you just noted that the end result is as though it did nothing?

What is the ActiveWorkbook when you run the code that fails?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[combo]

Do you have any BeforeSave procedure in processed workbook (actually, the [tt]ActiveWorkbook.Save[/tt] is not necessary, the process here is: export-delete-import-save)?
I agree with Tony, you could declare a variable (Workbook type) and assign processed workbook directly (with name) to it instead of changing ActiveWorkbook.

combo

 

[HughLerwill]

The following is copied from something that is working for me.

On Error Resume Next With .ActiveWorkbook.VBProject.VBComponents
.Remove .Item("Module1") 'error occurs if programmatic access to Visual Basic Project is not trusted in Excel's Macro security settings
End With
On Error GoTo 0

Hope it may help.

 

[Lightning]

Tony

Yes, I've been stepping through the code checking each line of the subroutine. Every other line of the code works exactly as expected. This line appears to process and the routine moves to the next line and continues processing until completion.

However, when watching the IDE window, while the line appears to process the module is not removed from the project, and when the .Import line runs, the new module is inserted as SubProcedures1. The file is then saved correctly.

As far as the ActiveWorkbook when the code is run, this routine is called from another sub which is looping through approximately 120 workbooks which need to be updated. The same thing happens no matter which workbook is active at the time. All of these workbooks are copies of the file I am trying to export the module from, just with earlier versions of the VBA code routines.

Combo
No, there are no BeforeSave procedures in the workbooks being updated, nor in the main workbook. There is a BeforeClose process, but that shouldn't be affecting this process. And yes, the ActiveWorkbook.Save isn't necessary. I put that line in to force the save on the off-chance that the IDE window simply wasn't refreshing and the module really had been deleted but the label was still showing. I just haven't taken the line out again yet.

Below is the entire code for the process I'm trying to run. It correctly executes and loops through the files and then displays the msgbox at the end of the routine. The only thing that doesn't happen is the old module being deleted. I have check the references and all required libraries are available. In the Security settings Trust access to Visual Basic Project is selected. Everything seems correct.

[COLOR=green]'''Module-level declarations[/color]
Dim strFileName As String, strFileName2 As String
Dim strFileFrom As String, strFileTo As String

Const HEALTHPATH As String = "J:\My Documents\Performance Period 1\1.1 Organisational Health\"

[b]Public Sub ReplaceOrgHealthModules()[/b][COLOR=navy] <---Begins Here[/color]

Dim strExportFrom As String
Dim strModuleName As String
Dim fs As Variant, x As Variant
Dim i As Integer, j As Integer, k As Integer

    i = 1
    '   Make sure access to the VBProject is allowed
    On Error Resume Next
    strFileFrom = ActiveWorkbook.Name
    Set x = ActiveWorkbook.VBProject
    If Err <> 0 Then
        MsgBox "Your security settings do not allow this macro to run.", vbCritical
        On Error GoTo 0
        Exit Sub
    End If
    Application.AutomationSecurity = msoAutomationSecurityLow

'2. Find all existing workbooks for Measure 1.1
    Set fs = Application.FileSearch
    With fs
        .NewSearch
        .LookIn = HEALTHPATH
        .Filename = "*.xls"
        .Execute

        If .Execute() > 0 Then
            For i = 1 To .FoundFiles.Count
                If InStr(1, .foundfile(i), "_QF1-1_") Then
                    strFileName = .FoundFiles(i)
                    Application.Workbooks.Open (strFileName)

                    Application.Run "'" & strFileName & "'!enablemenuItems"
                    Workbooks(strFileName).Activate
                    ActiveSheet.Range("U3").Value = "True"
                    Call UpdateTemplate

                End If
            Next i
        End If
    End With
    MsgBox "Update is complete", vbOKOnly + vbInformation, "Done..."

End Sub

[b]Sub UpdateTemplate()[/b]
    Filename = strFileName

'   Make sure access to the VBProject is allowed
    On Error Resume Next
    Set x = ActiveWorkbook.VBProject
    If Err <> 0 Then
        MsgBox "Your security settings do not allow this macro to run.", vbCritical
        On Error GoTo 0
        Exit Sub
    End If

'   Activate workboook
    Workbooks(strFileName).Activate
    Application.Run "'" & strFileName & "'!enablemenuItems"

    If Err <> 0 Then
        MsgBox Filename & " must be open!", vbCritical
        Exit Sub
    End If

        Call ReplaceModules
End Sub

[b]Sub ReplaceModules()[/b]
'   Export Module from this workbook
    strFileName2 = ThisWorkbook.PATH & "\tempmodxxx.bas"
    ThisWorkbook.VBProject.VBComponents("PublicSubprocedures").Export strFileName2

'   Replace Module in UserBook
    Set VBP = ActiveWorkbook.VBProject
    Debug.Print ActiveWorkbook.Name
    On Error GoTo ErrHandle
    With VBP.VBComponents
        .Remove VBP.VBComponents.Item("PublicSubprocedures")

        ActiveWorkbook.Save
        .Import strFileName2
    End With
    Application.EnableEvents = False
    ActiveWorkbook.Close savechanges:=True
    Application.EnableEvents = True

'   Delete the temorary module file
    Kill strFileName2

    MsgBox "The module has been replaced.", vbInformation
    Exit Sub

ErrHandle:
'   Did an error occur?
        MsgBox Err.Number & ":" & Err.Description, vbCritical
End Sub

Lightning

 

[combo]

You could try one of the following:

1. declare (module level) workbook variable, use it instead ActiveWorkbook:
[tt]Dim wbWorkbook as Workbook
' define in ReplaceOrgHealthModules()
Set wbWorkbook=Application.Workbooks.Open(strFileName)[/tt]

2. in test environment with proper file, remove all 'On Error...', run the code step by step,

3. if it is not necessary to run macros in processed files, set AutomationSecurity to msoAutomationSecurityForceDisable.

4. add new module, copy contents, delete old one and manually remove. Can the code replace it?

5. try DoEvents,

6. can you replace code directly, as in the link?:
[tt]With CodeM2
.DeleteLines 1, .CountOfLines
.InsertLines 1, CodeM1.Lines(1, CodeM1.CountOfLines)
End With[/tt]

combo