Copy Modules from Source Workbook to multiple Destination Workbooks

[jono_oh]

Hello all,

I have a workbook containing 15 VBA modules and wish to copy them into multiple other workbooks with a particular naming convention (contain "TSP" in their name, do not contain "blank template" in their name). I am aware you can drag and drop modules while in Visual Basic, however as I currently have approximately 75 destination workbooks, I was hoping there was a way to automate this process.

Any help would be appreciated; FYI I have a method of looping through the files that meet the naming convention, more interested in how to copy Modules to other workbooks via a macro

 

[Domenic]

First, allow access to the VBA project object module . . .

File >> Options >> Trust Center >> Trust Center Settings >> Macro Settings >> Developer Macro Settings

. . . and select/check Trust access to the VBA project object module .

Then, first create a temporary folder so that you can export all of the regular modules from your source workbook to this temporary folder . . .

    Dim tempFolder As String
    tempFolder = Environ("temp") & "\temp\"
    If Right(tempFolder, 1) <> "\" Then
        tempFolder = tempFolder & "\"
    End If
   
    On Error Resume Next
    MkDir tempFolder
    On Error GoTo 0

Then, export of all of the regular modules from your source workbook to the temporary folder. Assuming that wbSource has been assigned the source workbook . . .

    With wbSource.VBProject
   
        ReDim moduleNames(1 To .VBComponents.Count) As String
        Dim moduleCount As Long
        Dim vbc As Object
       
        moduleCount = 0
        For Each vbc In .VBComponents
            If vbc.Type = 1 Then 'vbext_ct_StdModule
                moduleCount = moduleCount + 1
                moduleNames(moduleCount) = tempFolder & vbc.Name
                vbc.Export tempFolder & vbc.Name
            End If
        Next vbc
       
    End With

Then, make sure that the source workbook did indeed contain regular modules. If not, exit the sub . . .

    If moduleCount = 0 Then
        MsgBox "No standard modules found . . .", vbInformation
        GoTo exitHandler
    End If

Then, loop through each workbook, and pass the workbook and moduleNames array to the ImportModules function for processing . . .

    ReDim Preserve moduleNames(1 To moduleCount)
   
    'loop through each workbook
   
        'open workbook
       
        If Not ImportModules(wbDestination, moduleNames) Then
            wbDestination.Close SaveChanges:=False
            MsgBox "Unable to import modules into " & wbDestination.Name & "." & vbCrLf & vbCrLf & "Aborting procedure . . .", vbExclamation, "Abort Procedure"
            GoTo exitHandler
        End If
       
        'close workbook
   
    'next workbook
   
    MsgBox "Completed . . .", vbInformation
   
exitHandler:
        On Error Resume Next
        Kill tempFolder & "*.*"
        RmDir tempFolder
        On Error GoTo 0

And here's the function . . .

Public Function ImportModules(ByVal wb As Workbook, ByRef moduleNames() As String) As Boolean

    On Error GoTo errHandler
   
    Dim i As Long
    For i = LBound(moduleNames) To UBound(moduleNames)
        wb.VBProject.VBComponents.Import moduleNames(i)
    Next i
   
    ImportModules = True
   
    Exit Function
   
errHandler:
    ImportModules = False
       
End Function

Hope this helps!

 

[jono_oh]

First, allow access to the VBA project object module . . .

File >> Options >> Trust Center >> Trust Center Settings >> Macro Settings >> Developer Macro Settings

. . . and select/check Trust access to the VBA project object module .

Then, first create a temporary folder so that you can export all of the regular modules from your source workbook to this temporary folder . . .

    Dim tempFolder As String
    tempFolder = Environ("temp") & "\temp\"
    If Right(tempFolder, 1) <> "\" Then
        tempFolder = tempFolder & "\"
    End If
  
    On Error Resume Next
    MkDir tempFolder
    On Error GoTo 0

Then, export of all of the regular modules from your source workbook to the temporary folder. Assuming that wbSource has been assigned the source workbook . . .

    With wbSource.VBProject
  
        ReDim moduleNames(1 To .VBComponents.Count) As String
        Dim moduleCount As Long
        Dim vbc As Object
      
        moduleCount = 0
        For Each vbc In .VBComponents
            If vbc.Type = 1 Then 'vbext_ct_StdModule
                moduleCount = moduleCount + 1
                moduleNames(moduleCount) = tempFolder & vbc.Name
                vbc.Export tempFolder & vbc.Name
            End If
        Next vbc
      
    End With

Then, make sure that the source workbook did indeed contain regular modules. If not, exit the sub . . .

    If moduleCount = 0 Then
        MsgBox "No standard modules found . . .", vbInformation
        GoTo exitHandler
    End If

Then, loop through each workbook, and pass the workbook and moduleNames array to the ImportModules function for processing . . .

    ReDim Preserve moduleNames(1 To moduleCount)
  
    'loop through each workbook
  
        'open workbook
      
        If Not ImportModules(wbDestination, moduleNames) Then
            wbDestination.Close SaveChanges:=False
            MsgBox "Unable to import modules into " & wbDestination.Name & "." & vbCrLf & vbCrLf & "Aborting procedure . . .", vbExclamation, "Abort Procedure"
            GoTo exitHandler
        End If
      
        'close workbook
  
    'next workbook
  
    MsgBox "Completed . . .", vbInformation
  
exitHandler:
        On Error Resume Next
        Kill tempFolder & "*.*"
        RmDir tempFolder
        On Error GoTo 0

And here's the function . . .

Public Function ImportModules(ByVal wb As Workbook, ByRef moduleNames() As String) As Boolean

    On Error GoTo errHandler
  
    Dim i As Long
    For i = LBound(moduleNames) To UBound(moduleNames)
        wb.VBProject.VBComponents.Import moduleNames(i)
    Next i
  
    ImportModules = True
  
    Exit Function
  
errHandler:
    ImportModules = False
      
End Function

Hope this helps!

Thank you so much for this Domenic, this has helped me out so much! Appreciate you taking the time to explain this so clearly

 

[Domenic]

That's great, I'm happy to help. And thanks for your feedback.

Cheers!