VBA to automatically replace Modules in several workbooks

Question

Someone posted a question on mrexcel, asking how to replace modules in existing workbooks with new ones: https://www.mrexcel.com/forum/excel-questions/760732-vba-automatically-replace-modules-several-workbooks.html

They answered their question with others support as follows:

Sub Update_Workbooks()
'This macro requires that a reference to Microsoft Scripting Routine
'be selected under Tools\References in order for it to work.
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim fso As New FileSystemObject
Dim source As Scripting.Folder
Dim wbFile As Scripting.File
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim Filename As String
Dim ModuleFile As String
Dim Element As Object
Set source = fso.GetFolder("C:\Users\Desktop\Testing")   'we will know this since all of the files will be in one folder
For Each wbFile In source.Files
If fso.GetExtensionName(wbFile.Name) = "xlsm" Then  'we will konw this too. All files will be .xlsm
Set book = Workbooks.Open(wbFile.path)
    Filename = FileNameOnly(wbFile.Name)
    'This will remove all modules including ClassModules and UserForms.
    'It will keep all object modules like (sheets, ThisWorkbook)
    On Error Resume Next
    For Each Element In ActiveWorkbook.VBProject.VBComponents
        ActiveWorkbook.VBProject.VBComponents.Remove Element
        Next

    On Error GoTo ErrHandle
'   Export Module1 from updating workbook
    ModuleFile = Application.DefaultFilePath & "\tempmodxxx.bas"
    Workbooks("Update Multiple Workbooks.xlsm").VBProject.VBComponents("Module1") _
    .Export ModuleFile
'   Replace Module1 in Userbook
    Set VBP = Workbooks(Filename).VBProject
    On Error Resume Next
    With VBP.VBComponents
        .Import ModuleFile
    End With
'   Delete the temporary module file
    Kill ModuleFile

book.Close True
End If
Next
    Exit Sub
ErrHandle:
'   Did an error occur?
    MsgBox "ERROR. The module may not have been replaced.", _
      vbCritical
End Sub

However, its quite large, and wanted to show a simple way of doing the same thing. Also, I found that when Importing the Modules to a different sheet, the ThisWorkBook and Sheet files are also imported as ClassModules. This is not always desired, so see answer below for alternative options!

Answer 1

You can import (or export if you flip the order) Modules from a different sheet using the following Sub:

Sub import_mods()
'First define each module you're looking to 
'take from the excel sheet "Workbook_with_Modules.xlsm"

For Each Element In Workbooks("Workbook_with_Modules.xlsm").VBProject.VBComponents

    'MsgBox Element.Name 'I ran this first to see which modules are available

'First, export each module from the "Workbook_with_Modules.xlsm"
Workbooks("Workbook_with_Modules.xlsm").VBProject.VBComponents(Element.Name).Export (Element.Name)

'Then, Import them into the current Workbook
 Workbooks(ThisWorkbook.Name).VBProject.VBComponents.Import (Element.Name)
Next Element

End Sub

I created a separate sub to delete the one's I'm not interested in keeping. You can Call it directly from the previous sub if you prefer, or build the If statement for the type into the previous sub as well, but for this example's sake, its a separate Sub entirely.

Sub rems()
'Types:
'   100 = Sheets and ThisWorkbook for current Workbook
'   1 = Modules (such as "Module1")
'   2 = ClassModules (such as other sheets from a different Workbook "ThisWorkBook1")

For Each Element In Workbooks(ThisWorkbook.Name).VBProject.VBComponents
'I first tested the types and corresponding number
'MsgBox Workbooks(ThisWorkbook.Name).VBProject.VBComponents(Element.Name).Type

'Now, the If function for removing all ClassModules (Type = 2)
        If Workbooks(ThisWorkbook.Name).VBProject.VBComponents(Element.Name).Type = 2 Then
            Workbooks(ThisWorkbook.Name).VBProject.VBComponents.Remove Element
        End If        
Next Element

End Sub

Hope this helps anyone!