|
- Sub test1()
-
- Dim p As String, f
- Dim Cnn As Object, Rst As Object, Sql As String
- Dim i As Long, ky As String, s As String, t As String
-
- Set Cnn = CreateObject("ADODB.Connection")
- Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0 XML;Data Source=" & ThisWorkbook.FullName
-
- Cells.Clear
-
- p = ThisWorkbook.Path & "\"
- f = Array("A.xlsx", "B.xlsx", "C.xlsx")
-
- Set Rst = Cnn.Execute("SELECT * FROM [Excel 12.0;Database=" & p & f(1) & "].[$A1:J] WHERE FALSE")
- For i = 0 To Rst.Fields.Count - 2
- t = Rst.Fields(i).Name
- s = s & "a." & t & ","
- Range("A1").Offset(0, i) = t
- If i = 2 Then ky = t
- Next
- t = Rst.Fields(i).Name
- s = s & "b." & t
- Range("A1").Offset(0, i) = t
-
- Sql = "SELECT " & s & " FROM " & _
- "[Excel 12.0;Database=" & p & f(1) & "].[$A1:J] a" & _
- " LEFT JOIN " & _
- "[Excel 12.0;Database=" & p & f(0) & "].[$A1:J] b" & _
- " ON a." & t & "=b." & t
- Set Rst = Cnn.Execute(Sql)
- Range("A2").CopyFromRecordset Rst
-
- Sql = "SELECT DISTINCT " & ky & " FROM [Excel 12.0;Database=" & p & f(1) & "].[$A1:J] WHERE LEN(" & ky & ")"
- Sql = "SELECT * FROM [Excel 12.0;Database=" & p & f(0) & "].[$A1:J] WHERE " & ky & " NOT IN (" & Sql & ")"
- Set Rst = Cnn.Execute(Sql)
- Range("C65536").End(xlUp).Offset(1, -2).CopyFromRecordset Rst
-
- If Dir(p & f(2)) <> "" Then Kill p & f(2)
- Cnn.Execute "SELECT * INTO [" & p & f(2) & "].[Sheet1] FROM [" & ActiveSheet.Name & "$]"
-
- Set Rst = Nothing
- Cnn.Close
- Set Cnn = Nothing
- Beep
- End Sub
复制代码 |
|