|
Sub excel連接數(shù)據(jù)庫() Dim Con As New ADODB.Connection Dim strCon As String Dim rs As ADODB.Recordset '設(shè)置記錄集 Dim i, t t = Timer strCon = " Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source= D:\平臺\報(bào)表\賬戶流水.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 '逐個(gè)字段 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 "提取完畢" & "耗時(shí)" & Round(Timer - t, 4) & "秒" End Sub Sub access連接并查詢() Dim Con As New ADODB.Connection Dim strConn As String Dim rs As ADODB.Recordset '設(shè)置記錄集 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 '逐個(gè)字段 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 "提取完畢" & "耗時(shí)" & Round(Timer - t, 4) & "秒" End Sub Sub sqlserver連接并查詢() Dim Con As New ADODB.Connection Dim strCon As String Dim rs As ADODB.Recordset '設(shè)置記錄集 Dim i, t t = Timer '連接遠(yuǎn)程數(shù)據(jù)庫 strCon = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _ "User ID=sa;Pwd=123456;" & _ "Initial Catalog= gydataH5 ;Data Source= 192.168.1.5 ;" '連接本地?cái)?shù)據(jù)庫 ' 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 '逐個(gè)字段 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 "提取完畢" & "耗時(shí)" & Round(Timer - t, 4) & "秒" End Sub |
|
|
