VBA将模块从一个Excel工作簿复制到另一工作簿 [英] VBA to copy Module from one Excel Workbook to another Workbook

问题描述

我正在尝试使用VBA将一个模块从一个excel工作簿复制到另一个.

I am trying to copy a module from one excel workbook to another using VBA.

我的代码:

'Copy Macros

Dim comp As Object
Set comp = ThisWorkbook.VBProject.VBComponents("Module2")
Set Target = Workbooks("Food Specials Rolling Depot Memo 46 - 01.xlsm").VBProject.VBComponents.Add(1)

出于某种原因，这会复制模块，但不会复制内部的VBA代码，为什么?

For some reason, this copies the module, but does not copy the VBA code inside, why?

请有人可以告诉我我要去哪里了吗?

Please can someone show me where i am going wrong?

谢谢

推荐答案

Sub CopyModule，接收3个参数:

1.Source Workbook(如Workbook).

1.Source Workbook (as Workbook).

2.要复制的模块名称(如String).

2.Module Name to Copy (as String).

3.Target Workbook(如Workbook).

3.Target Workbook (as Workbook).

Public Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As Workbook)

    ' Description:  copies a module from one workbook to another
    ' example: CopyModule Workbooks(ThisWorkbook), "Module2",
    '          Workbooks("Food Specials Rolling Depot Memo 46 - 01.xlsm")
    ' Notes:   If Module to be copied already exists, it is removed first,
    '          and afterwards copied

    Dim strFolder                       As String
    Dim strTempFile                     As String
    Dim FName                           As String

    If Trim(strModuleName) = vbNullString Then
        Exit Sub
    End If

    If TargetWB Is Nothing Then
        MsgBox "Error: Target Workbook " & TargetWB.Name & " doesn't exist (or closed)", vbCritical
        Exit Sub
    End If

    strFolder = SourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir

    ' create temp file and copy "Module2" into it
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"

    On Error Resume Next
    FName = Environ("Temp") & "\" & strModuleName & ".bas"
    If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
        Err.Clear
        Kill FName
        If Err.Number <> 0 Then
            MsgBox "Error copying module " & strModuleName & "  from Workbook " & SourceWB.Name & " to Workbook " & TargetWB.Name, vbInformation
            Exit Sub
        End If
    End If

    ' remove "Module2" if already exits in destination workbook
    With TargetWB.VBProject.VBComponents
        .Remove .Item(strModuleName)
    End With

    ' copy "Module2" from temp file to destination workbook
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile

    Kill strTempFile
    On Error GoTo 0

End Sub

---

主要Sub代码(用于使用邮政数据运行此代码):

---

Main Sub Code (for running this code with the Post's data):

Option Explicit

Public Sub Main()

Dim WB1 As Workbook
Dim WB2 As Workbook

Set WB1 = ThisWorkbook
Set WB2 = Workbooks("Food Specials Rolling Depot Memo 46 - 01.xlsm")

Call CopyModule(WB1, "Module2", WB2)

End Sub

这篇关于VBA将模块从一个Excel工作簿复制到另一工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！