Replace Module w/ VBA

[canuck13]

Hello, I have several hundred files with identical code on them. I need them to be able to operate independently so I can't use references to code from other files. I am looking for a way to loop through the files and make the same change to the VBA. The easiest I could find seemed to be to remove the module from the file then replace with a module of the same name from a new file with the edit. I created a test environment where I put three files in the folder "Test" and ran the following from a file outside of that folder. It runs without error. It loops through each file and imports the module from the external file. It just doesn't seem to remove the original module so the new Module becomes Module11 instead of Module1 and Module1 Remains unchanged. As you'll note I used a message box to make sure the variables were correct. I also tried removing the variables (left that commented as well) with same result. When I put that line in the Immediate window though and run with one of the test files activated the Module is removed as intended. Any ideas what is going on and how to fix it? I'm happy to go an entirely different direction as well to achieve the copy and paste of VBA, just seems like I'm 99% of the way there. Thanks in advance for any assistance you can provide!

Sub ReplaceModule()
'Replaces module in files with the module here with the same name
'Prep
    Dim modName As String
    Dim wb As Workbook
   
'USER ENTRY (Name of module you want to update--must be the same name in the old and new file)
    modName = "Module1"
       
    'Enter suffix (file type)
    NextFile = Dir("H:\Test\*.xlsm")
    While NextFile <> ""
        Workbooks.Open ("H:\Test\" + NextFile), UpdateLinks:=3
        Set wb = ActiveWorkbook
       
        'Checks of file before running code
        If wb.ReadOnly Then
            If MsgBox("File already in use! Click OK to continue on to next file. Click Cancel to exit code.", vbOKCancel) = vbCancel Then
                Exit Sub
            Else
                'Closes without saving and moves onto next file.
                Application.DisplayAlerts = False
                wb.Close
                Application.DisplayAlerts = True
                GoTo BadCheck:
            End If
        End If
              
        'Remove old version of module
               
                MsgBox wb.Name & " " & modName
                wb.VBProject.VBComponents.Remove wb.VBProject.VBComponents(modName) 'THIS IS THE LINE THAT DOESN'T WORK
'TRIED THIS TOO                ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("Module1")

        'Import new version of module
                Workbooks(ThisWorkbook.Name).VBProject.VBComponents(modName).Export (modName) 'Export new version from this file
                wb.VBProject.VBComponents.Import (modName) 'Import into "NextFile"

NextFile:
        Workbooks(NextFile).Save
        Workbooks(NextFile).Close
'Label for moving to next file if canceling update due to failed check above
BadCheck:
        NextFile = Dir()
    Wend

End Sub

 

[rollis13]

If it helps, your macro works for me; the only istance it didn't was when there where no module named 'Module1' to be removed in the opened file or when the VBAProject in the opened file was Locked with View protected.

 

[canuck13]

If it helps, your macro works for me; the only istance it didn't was when there where no module named 'Module1' to be removed in the opened file or when the VBAProject in the opened file was Locked with View protected.

Thanks for trying it out rollis. You confirmed that the module actually got replaced? Just asking because like I said, the code appears to run fine, it just doesn't make the change. I'm ok with the other errors you ran into since it would only be me using this procedure.

 

[rollis13]

I created a dummy Module1 and after all was done found the same module and code in the files that had been opened by the loop.
Why don't you do the same ? dummy Module1 and dummy folder with dummy files with different Module1.

 

[canuck13]

I created a dummy Module1 and after all was done found the same module and code in the files that had been opened by the loop.
Why don't you do the same ? dummy Module1 and dummy folder with dummy files with different Module1.

Pretty much what I did. One line sub A1=A1+1 in each of my test files. A1=A1+100 as the "new" macro. It was still A1=A1+1 which is how I know that it was still the old macro. I also called the one macro in Module1 in Sheet1 on a selection change event just to make sure that wouldn't cause any issues.

 

[rollis13]

One line sub A1=A1+1 in each of my test files. A1=A1+100 as the "new" macro.

In this case, would A1 be a variable of your macro ? If not, sorry but now I can't realize what you are trying to substitute. Need, at least, to see your macro in Module1.

 

[canuck13]

Sorry, I think adding in my dummy macro, particularly in short hand, just confused things. It could have been anything but I chose to use a selection change event on Sheet1 that would call a macro in Module1. The macro in the old module was

Sheet1.Range("A1")=Sheet1.Range("A1")+1

In the new macro I changed it to

Sheet1.Range("A1")=Sheet1.Range("A1")+100

My point was I wanted to make sure a reference to a macro that would not exist for a split second after the old one was deleted and before the new one was replaced wouldn't cause any issues. I wouldn't be calling it until the new macro was available so I can't imagine it would be an issue but wanted to be sure. I knew the macro had never been substituted since it still had the old version of the macro after running the routine above (not to mention a new Module11).

 

[rollis13]

As you said you have all the time to substitute the macro, the receiver isn't a running project. Anyway, if it were a running project you probably wouldn't be able to have access to it.

 

[canuck13]

So I discovered something interesting as I came back to this problem. When I run the code step by step the Module I am trying to remove remains all the way through even after save and close UNLESS I stop the code at which point it immediately goes away. That would explain why immediate works and the code does not. I tried moving the one line to another macro and calling it, hoping that if it finished executing that macro it would remove the module at that point. No go. So now my question is is there a way to somehow stop the code after the module is removed to that the module goes away but then automatically trigger a second macro that will do the replacing and saving? PArticularly with the loop to make the change to all files in the folder I'm concerned that that could be complicated.

 

[canuck13]

Success!!! If I RUN the separate macro as opposed to calling it, it works. Thanks rollis for testing it out, that got me on the right track. Just for future viewers here was my final code that worked:

Private ModName As String
Private wb As Workbook

Sub ReplaceModule()
'Replaces module in files with the module here with the same name
'USER ENTRY (Name of module you want to update--must be the same name in the old and new file)
    ModName = "Module1"
        
    'Enter suffix (file type)
    NextFile = Dir("H:\Test\*.xlsm")
    While NextFile <> ""
        Workbooks.Open ("H:\Test\" + NextFile), UpdateLinks:=3
        Set wb = ActiveWorkbook
        
    'Checks of file before running code
        If wb.ReadOnly Then
            If MsgBox("File already in use! Click OK to continue on to next file. Click Cancel to exit code.", vbOKCancel) = vbCancel Then
                Exit Sub
            Else
                'Closes without saving and moves onto next file.
                Application.DisplayAlerts = False
                wb.Close
                Application.DisplayAlerts = True
                GoTo BadCheck:
            End If
        End If
               
        'Remove old version of module (needs to run as its own macro as module is not removed until macro has fully executed)
            Application.Run ThisWorkbook.Name & "!RemoveModule"

        'Import new version of module
            Workbooks(ThisWorkbook.Name).VBProject.VBComponents(ModName).Export (ModName) 'Export new version from this file
            wb.VBProject.VBComponents.Import (ModName) 'Import into "NextFile"

NextFile:
        'Exit Design Mode
        Application.EnableEvents = True
        
        Workbooks(NextFile).Save
        Workbooks(NextFile).Close
'Label for moving to next file if canceling update due to failed check above
BadCheck:
        NextFile = Dir()
    Wend

End Sub


Sub RemoveModule()
    wb.VBProject.VBComponents.Remove wb.VBProject.VBComponents(ModName)
End Sub