Sub GetData()?????
???? Dim strConn As String, strSQL As String?
???? Dim conn As ADODB.Connection?
???? Dim ds As ADODB.Recordset?
???? Dim col As Integer?
?????
???? '清空電子表格的所有數(shù)據(jù)? ?????
??? Cells.Clear?
?????
???? '連接數(shù)據(jù)庫的字符串? ?????
??? strConn = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=name;Password=pwd;Initial Catalog=dataname;Data Source=servername"?
???? '查詢語句 ?
???? strSQL = "select * from table1"?
???? Set conn = New ADODB.Connection?
???? Set ds = New ADODB.Recordset?
???? '打開數(shù)據(jù)庫連接 ?
???? conn.Open strConn?
?????
???? With ds?
??? '根據(jù)查詢語句獲得數(shù)據(jù)?
???????? .Open strSQL, conn?
?????????
????????? '自動控制加入所有列標(biāo)題 ?
???????? For col = 0 To ds.Fields.Count - 1?
???? '請注意Offset(0, col)中的參數(shù)一定要正確噢 ?
???????????? Range("A1").Offset(0, col).Value = ds.Fields(col).Name?
???????? Next?
?????????
???????? '加入所有行數(shù)據(jù)?
???????? Range("a1").Offset(1, 0).CopyFromRecordset ds?
???? End With?
?????
???? '以下是關(guān)閉數(shù)據(jù)庫連接和清空資源 ?
???? Set ds = Nothing?
???? conn.Close?
???? Set conn = Nothing?
End Sub?

做完上面的模塊后,要調(diào)用它就要加入一個事件:?
'我這個是加入一個工作薄打開時就運行GetData方法,也就是自動加載數(shù)據(jù) ?
Private Sub Workbook_Open()?
???? Application.Run "GetData"?
End Sub

總結(jié)

以上是生活随笔為你收集整理的Excel的VBA连接数据库方法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。