Sub excel连接数据库()
Dim Con As New ADODB.Connection
Dim strCon As String
Dim rs As ADODB.Recordset '设置记录集
Dim i, t
t = Timer
strCon = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= D:\平台\报表\账户流水.xlsx" & _
";Extended Properties=""Excel 12.0;HDR=True"";"
strSQL = "select * from [账户流水$]"
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
Range("c4").CopyFromRecordset rs
rs.Close
Con.Close
Set rs = Nothing
Set Con = Nothing
MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"
End Sub
Sub access连接并查询()
Dim Con As New ADODB.Connection
Dim strConn As String
Dim rs As ADODB.Recordset '设置记录集
Dim i, t
t = Timer
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\Administrator\Desktop\test.accdb"
strSQL = "select * from 账户流水"
Con.Open strConn
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
Range("c4").CopyFromRecordset rs
rs.Close
Con.Close
Set rs = Nothing
Set Con = Nothing
MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"
End Sub
Sub sqlserver连接并查询()
Dim Con As New ADODB.Connection
Dim strCon As String
Dim rs As ADODB.Recordset '设置记录集
Dim i, t
t = Timer
'连接远程数据库
strCon = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
"User ID=sa;Pwd=123456;" & _
"Initial Catalog= gydataH5 ;Data Source= 192.168.1.5 ;"
'连接本地数据库
' strCon = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
' "User ID=sa;Pwd=;" & _
' "Initial Catalog= AdventureWorks2008;Integrated Security=SSPI"
strSQL = "Select * from smscodelog"
'strSQL = "Select * from person.person"
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
Range("c4").CopyFromRecordset rs
rs.Close
Con.Close
Set rs = Nothing
Set Con = Nothing
MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"
End Sub
转载请注明原文地址: https://ju.6miu.com/read-962789.html
