Replace Module w/ VBA

canuck13

New Member
Joined
Oct 8, 2014
Messages
31
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!

VBA Code:
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

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Since you are to Close without Saving shouldn't it be:
VBA Code:
'[...]
'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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Threads
1,129,681
Messages
5,637,760
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top