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
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