Copy Module between two open workbooks

gto70ski
2StarLounger
Posts: 135
Joined: 04 Mar 2019, 19:20

Copy Module between two open workbooks

Post by 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!!

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy Module between two open workbooks

Post by 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.
Best wishes,
Hans

gto70ski
2StarLounger
Posts: 135
Joined: 04 Mar 2019, 19:20

Copy Module between two open workbooks

Post by gto70ski »

Awesome!

Works great!

Thanks again!

gto70ski
2StarLounger
Posts: 135
Joined: 04 Mar 2019, 19:20

Copy Module between two open workbooks

Post by 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!

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy Module between two open workbooks

Post by 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.
Best wishes,
Hans

gto70ski
2StarLounger
Posts: 135
Joined: 04 Mar 2019, 19:20

Copy Module between two open workbooks

Post by 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

User avatar
macropod
4StarLounger
Posts: 508
Joined: 17 Dec 2010, 03:14

Re: Copy Module between two open workbooks

Post by 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.
Paul Edstein
[Fmr MS MVP - Word]

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy Module between two open workbooks

Post by HansV »

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

gto70ski
2StarLounger
Posts: 135
Joined: 04 Mar 2019, 19:20

Copy Module between two open workbooks

Post by gto70ski »

It is also a .xlsm file type.

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy Module between two open workbooks

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4907
Joined: 31 Aug 2016, 09:02

Re: Copy Module between two open workbooks

Post by YasserKhalil »

The code works well for me too ...

gto70ski
2StarLounger
Posts: 135
Joined: 04 Mar 2019, 19:20

Copy Module between two open workbooks

Post by 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
2StarLounger
Posts: 135
Joined: 04 Mar 2019, 19:20

Copy Module between two open workbooks

Post by 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

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy Module between two open workbooks

Post by HansV »

Thanks for sharing.
Best wishes,
Hans