excel vba+sql多表连接代码 （full left right join)

Option Explicit


Sub excel连接数据库()




    Dim Con As New ADODB.Connection
    Dim strCon, strsql As String
    Dim rs As ADODB.Recordset  '设置记录集
    Dim i, t
    
    t = Timer
   
    strCon = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source= C:\Users\Administrator\Desktop\a1.xlsx" & _
                ";Extended Properties=""Excel 12.0;HDR=True"";"
   
   


    strsql = "select a.id,a.firstname,b.lastname from [a1$] a left join [Excel 8.0;hdr=1;imex=1;Database=C:\Users\Administrator\Desktop\a2.xlsx].[a2$] b  on a.id=b.id"
   
    Con.Open strCon
    
    Set rs = Con.Execute(strsql)
    
    
    For i = 0 To rs.Fields.Count - 1 '逐个字段
        Cells(3, i + 3) = rs.Fields(i).Name '取字段名  字头放置在cell(3,3)
    Next i
    
    Cells(4, 3).CopyFromRecordset rs
     
    rs.Close
    Con.Close
    
    Set rs = Nothing
    Set Con = Nothing
    
    MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"
    
End Sub