Excel中通过VBA读取SQLServer数据库数据加载到⼯作表代码如图:
附代码:
========================================
'读取数据库数据并写到Sheet中
Public Sub Exports()
Dim rowIndex
rowIndex = 2
With Sheet1
.Range("1:65536").Clear
.Range("A1").Value = "员⼯姓名"
.Range("B1").Value = "所属部门"
.
Range("C1").Value = "⼿机号码"
excel连接sql数据库教程End With
Set rs = ConnObject.Execute("SELECT t1.YGXM,t1.SJHM,t2.BMMC FROM RS_YGB AS t1 INNER JOIN RS_BMB AS t2 ON t1.SSBM=t2.BMBH")
While Not rs.EOF
With Sheet1
.Range("A" & rowIndex).Value = rs("YGXM")
.Range("B" & rowIndex).Value = rs("BMMC")
.Range("C" & rowIndex).Value = rs("SJHM")
End With
rowIndex = rowIndex + 1
rs.MoveNext
Wend
FormatAll
End Sub
' 创建数据库链接对象
Private Function ConnObject()
Dim strConn
Dim objConn
strConn = ""
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=10.90.0.2;UID=oasa;PWD=lixinyue112233;DATABASE=OA"
Set ConnObject = objConn
End Function
========================================