Excel vba根据条件合并

数据量不多的话没必要通过字典过桥，直接借用一个空的工作表作为过桥即可，我是通过SHEET3

代码如下

Sub 父子关系1()

On Error Resume Next

Sheets("Sheet3").Cells.Select

Selection.ListObject.QueryTable.Delete

Sheets("Sheet3").Select

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _

"ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\xd\桌面\工作簿1.xlsm;DefaultDir=C:\Documents and Settings\xd\桌面;DriverId=1046;MaxBuffe" _

), Array("rSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable

.CommandText = Array( _

"SELECT AA.A, AA.B, BB.A, BB.B" & Chr(13) & "" & Chr(10) & "FROM AA AA, BB BB" & Chr(13) & "" & Chr(10) & "WHERE AA.B = BB.A")

.PreserveFormatting = True

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "表_查询来自_Excel_Files"

End With

Sheets("Sheet1").Select

For J = 1 To Sheet1.[b65536].End(xlUp).Row

For I = 1 To Sheet3.[b65536].End(xlUp).Row

If Sheet1.Cells(J, 4) <> "" And Sheet1.Cells(J, 4) = Sheet3.Cells(I, 4) And Sheet1.Cells(J, 2) <> Sheet3.Cells(I, 2) Then

TEMP = TEMP & Sheet3.Cells(I, 1)

End If

Next

Sheet1.Cells(J, 5) = TEMP

TEMP = ""

Next

End Sub

合并excel机器人可以自动将同一文件夹中的excel按自定义行数进行合并。合并excel：https://store.uibot.com.cn/robots/detail/918.html