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]

Since you are to Close without Saving shouldn't it be:

'[...]
'Closes without saving and moves onto next file.
Application.DisplayAlerts = False
'wb.Close
wb.Close SaveChanges:=False '<= changed
Application.DisplayAlerts = True
'[...]

I understand that if you are closing because the file is in use probably there is nothing to save but you never know.