VBA excel宏2文件在读取时处理合并错误

Question

我试图用下面的代码将两个Excel表合并到第三个Excel表中

`Sub CommandButton1_Click()
 Dim MyFile As String
 Dim Filepath As String
 Filepath = "C:\temp\"
 MyFile = Dir(Filepath)
  'MyFile = "12_10_15_par.xlxs"
     'If MyFile = "12_10_15_par.xlsx" Then
     If MyFile = Cells(4, 2) Then
        Workbooks.Open (Filepath & MyFile)
        Worksheets("par").Range("A1:K1000").Copy
        ActiveWorkbook.Close
        ActiveSheet.Paste Destination:=Worksheets("match").Range("T1:AF1001")
        'Application.CutCopyMode = False
     End If
     MyFile2 = Dir(Filepath)
     If MyFile2 = Cells(5, 2) Then
        Workbooks.Open (Filepath & MyFile2)
        Worksheets("ops").Range("A1:K1000").Copy
        ActiveWorkbook.Close
        ActiveSheet.Paste Destination:=Worksheets("match").Range("D1:S1001")
        'Application.CutCopyMode = False
     End If

 End Sub`

也许我做错了，因为Myfile2保留了第一个值，而没有得到新的值.

Answer 1

下面是如何使用Dir()，注意循环结束时的MyFile = Dir()，它将在循环之前在MyFile中加载下一个文件名！

试试看：

Sub CommandButton1_Click()
Dim MyFile As String, _
    Filepath As String, _
    oWb As Workbook, _
    pWb As Workbook

Filepath = "C:\temp\"
Set pWb = ThisWorkbook

MyFile = Dir(Filepath)
Do While MyFile <> ""
    If MyFile <> Cells(4, 2) And MyFile <> Cells(5, 2) Then
    Else
        Set oWb = Workbooks.Open(Filepath & MyFile)
        If MyFile <> Cells(5, 2) Then
            oWb.Worksheets("par").Range("A1:K1000").Copy Destination:=pWb.Worksheets("match").Range("T1:AF1001") '4
        Else
            oWb.Worksheets("ops").Range("A1:K1000").Copy Destination:=pWb.Worksheets("match").Range("D1:S1001") '5
        End If
        'Application.CutCopyMode = False
        oWb.Close
    End If
    MyFile = Dir()
Loop

End Sub