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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
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

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

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 8, 2014
Messages
31

ADVERTISEMENT

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

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 8, 2014
Messages
31

ADVERTISEMENT

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
VBA Code:
Sheet1.Range("A1")=Sheet1.Range("A1")+1
In the new macro I changed it to
VBA Code:
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

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
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

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

New Member
Joined
Oct 8, 2014
Messages
31
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:
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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