从多个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按钮

在Developer选项卡中，在Controls组中，单击插入下拉按钮，您会注意到有2个分组，上面一个具有标题Form Controls，下面一个具有标题ActiveX Controls。从ActiveX控件插入按钮。

Answer 2

如果您想运行特定于您正在执行的合并文件的宏，序言:

Call Thisworkbook.MacroName

请确保在执行之前初始化为工作簿中的正确页。你也可以用Thisworkbook来控制。

Thisworkbook.Sheetname