vba - 来自多个 Excel 工作簿的新组合工作簿中的宏链接更新

Question

我已按照以下代码合并工作簿数据:

Sub Merge2MultiSheets()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFilename As String
    Dim CurrentRow As Integer
    Dim CurrentColumn As Integer
    Dim Index As Integer

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "D:\Excels" ' change to suit

    CurrentRow = 1
    CurrentColumn = 1
    Index = 0
    Set wbSource = ActiveWorkbook
    strFilename = wbSource.Worksheets("Test").Cells(CurrentRow, Index + 1)

    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    'strFilename = Dir(MyPath & "\*.xlsx", vbNormal)

    If Len(strFilename) = 0 Then Exit Sub

    Do Until strFilename = ""

       Set wbSrc = Workbooks.Open(fileName:=MyPath & "\" & strFilename)

       Set wsSrc = wbSrc.Worksheets(1)

       wsSrc.Copy after:=wbDst.Worksheets(wbDst.Worksheets.Count)

       Set wsSrc = wbSrc.Worksheets(2)

       wsSrc.Copy after:=wbDst.Worksheets(wbDst.Worksheets.Count)

       wbSrc.Close False

       Index = Index + 1
       strFilename = wbSource.Worksheets("Test").Cells(CurrentRow, Index + 1)

    Loop
    wbDst.Worksheets(1).Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

现在的问题是，每个单独的工作簿都有一个带有宏的按钮，单击按钮上的一些代码在单个文件中工作正常，但在合并文件中，当我单击按钮时，它会打开原始文件来运行代码单击按钮，它没有使用合并文件中复制的代码。

让我知道如何确保合并的文件不依赖于原始文件来运行按钮单击代码，并且应该是独立的。

评论中询问的单独按钮点击代码:

Sub Calculate_Click()
    Dim sum As Integer
    Dim mult As Integer

    Dim input1 As Integer
    Dim input2 As Integer

    input1 = Worksheets("test1_input").Cells(1, 2)
    input2 = Worksheets("test1_input").Cells(2, 2)


    sum = input1 + input2
    mult = input1 * input2

    Worksheets("test1_output").Cells(1, 2) = sum
    Worksheets("test1_output").Cells(2, 2) = mult

End Sub

Answer 1

我测试了代码，问题似乎仅限于表单按钮。尝试使用 ActiveX 按钮

在开发人员选项卡的控件组中，单击“插入”下拉按钮时，您会注意到有 2 个分组，上面的一个带有标题表单控件以及标题为ActiveX 控件的下一个控件。从 ActiveX 控件插入按钮。