VBA怎样进行数据库的两表左交操作？

Question

sheet1表中有A、B、C三列，sheet2表中有A、D、E三列

现在需要在sheet3中生成两表左交的结果，这个VBA怎么写比较优雅？谢谢

Answer 1

Sub ExecuteSQL()

Dim cnn, rcd, sSQL, i

Set cnn = CreateObject("adodb.connection")

cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ActiveWorkbook.FullName

Set rcd = CreateObject("adodb.recordset")

sSQL = "SELECT T1.[A],T1.[B],T1.[C],T2.[D],T2.[E] FROM [Sheet1$] AS T1 LEFT JOIN [Sheet2$] AS T2 ON T1.[A]=T2.[A]"

Set rcd = cnn.Execute(sSQL)

With Sheet3
    .Cells.ClearContents

    For i = 1 To rcd.Fields.Count
        .Cells(1, i) = rcd.Fields(i - 1).Name
    Next
    
    .Cells(2, 1).CopyFromRecordset rcd
End With

rcd.Close
Set rcd = Nothing
cnn.Close
Set cnn = Nothing

End Sub