Copy Module between two open workbooks

[gto70ski]

I have seen a couple of VBA codes for copying a module from one workbook to another but what if the files are in a directory with a dozen or so other closed files and these two are the only ones open?

Say my source file name remains the same all the time and the target file names changes but both are open.

Thanks!!

[HansV]

Try this:

Code: Select all

Sub CopyModule()
    Dim strPath As String
    Dim strModule As String
    Dim strFile As String
    Dim wbk As Workbook
    ' Path to source workbook
    strPath = ThisWorkbook.Path & "\"
    ' Module to export
    strModule = "Module1"
    ' Path to export module to
    strFile = strPath & strModule & ".bas"
    ' Export module
    ThisWorkbook.VBProject.VBComponents(strModule).Export Filename:=strFile
    ' Loop through open workbooks
    For Each wbk In Workbooks
        ' Exclude source workbook, personal macro workbook and add-ins
        If wbk.Name <> ThisWorkbook.Name And wbk.Name <> "PERSONAL.XLSB" And wbk.IsAddin = False Then
            ' Import module
            wbk.VBProject.VBComponents.Import Filename:=strFile
            ' Save workbook
            wbk.Save
        End If
    Next wbk
    ' Delete exported module
    Kill strFile
End Sub

Change "Module1" to the name of the module that you want to copy.

[gto70ski]

Awesome!

Works great!

Thanks again!

[gto70ski]

Sorry I thought I checked it and thought it worked. I looked at the wrong file.

on the line: ThisWorkbook.VBProject.VBComponents(strModule).Export Filename:=strFile

it bombed with a "method 'export' of object '_VBComponent' failed" return with I stepped through it.

Thanks!

[HansV]

Does the code run when you simply run it instead of single-stepping through it? Code that manipulates code generally doesn't like to be single-stepped.

[gto70ski]

It runs through as if nothing is wrong, but it doesn't copy the module.

I single stepped it just trying to locate the issue of why it didn't copy.

Thanks

[macropod]

If you add the code you're trying to migrate to the template used by the workbooks, you don't need to do the migration. Plus you don't then need to save those workbooks in the xlsm format.

[HansV]

Was the other workbook to which the module should be copied a .xlsx workbook or a .xlsm workbook?

[gto70ski]

It is also a .xlsm file type.

[HansV]

Strange - when I tested the code, it did copy the module to the other open workbook.

One more thing to check:
- Select File > Options.
- Select Trust Center, then click Trust Center Settings...
- Select Macro Settings.
- Make sure that the check box "Trust access to the VBA project object model" is ticked.
- OK your way out.

S2978.png

[YasserKhalil]

The code works well for me too ...

[gto70ski]

Alright finally got some positive results. I copied the files to my local computer and they work fine there.

I had been using a corporate shared directory for running the code and that evidently is the issue.

Even with only two files on the corporate shared directory folder, the code will run through when called but will not copy the module.

Thanks Again ever so much for your help!!

[gto70ski]

The problem lies in the permissions on the shared server:

Below is a revision to use the user's desktop as a temporary placement area.

Code: Select all

Sub CopyModule()
'copy module module mod_CFandDV to the eREV worksheet
    Dim strPath As String
    Dim strModule As String
    Dim strFile As String
    Dim wbk As Workbook
    ' Path to source workbook
    strPath = ThisWorkbook.Path & "\"
    ' Module to export
    strModule = "mod_CFandDV"
    ' Path to export module to
    Dim strExportFolder As String
    strExportFolder = Environ("userprofile") & "\Desktop\ExportedVBA"
   strFile = strExportFolder & strModule & ".bas"
    'strFile = strPath & strModule & ".bas"
    ' Export module
    ThisWorkbook.VBProject.VBComponents(strModule).Export Filename:=strFile
    ' Loop through open workbooks
    For Each wbk In Workbooks
        ' Exclude source workbook, personal macro workbook and add-ins
        If wbk.Name <> ThisWorkbook.Name And wbk.Name <> "PERSONAL.XLSB" And wbk.IsAddin = False Then
            ' Import module
            wbk.VBProject.VBComponents.Import Filename:=strFile
            ' Save workbook
            wbk.Save
        End If
    Next wbk
    ' Delete exported module
    Kill strFile
End Sub

[HansV]

Thanks for sharing.