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