先在在工具栏右键选控件工具箱，再插入一个按钮来触发VBA的函数，双击按钮，进行代码编辑，在“工具”->“引用”添加Microsoft ActiveX Data 2.8 library和下面的Microsoft ActiveX Data RecordSet 2.8 library
再用如下代码：
1
Private Sub CommandButton1_Click()
2
3'------db prepare-----
4 Dim conn, vcc, sql
5
6 sqlb = "select * from vba_test "
7 Set conn = New ADODB.Connection
8 conn.Open "driver={sql server};server=(local);UID=mydb;PWD=user; database=pass"
9 Set vcc = New ADODB.Recordset
10
11'---- fill------
12 Dim msg
13 If Application.Documents(1).Tables.Count >= 1 Then
14 Dim tb, name, result
15 Set tb = Application.Documents(1).Tables(1)
16 Dim i
17 For i = 1 To tb.Rows.Count
18 name = tb.Cell(i, 1).Range.Text
19 sql = sqlb & "where name='" & Left(name, Len(name) - 2) & "'"
20 vcc.Open sql, conn, 3, 2
21 ' MsgBox vcc = Null
22 If Not vcc.EOF Then
23 result = vcc("info") & " "
24 tb.Cell(i, 2).Range.Text = result
25 End If
26 vcc.Close
27 Next
28
29 Set tb = Nothing
30 End If
31
32''--------clean up
33 Set conn = Nothing
34 Set vcc = Nothing
35End Sub
36
37
Private Sub CommandButton1_Click()2
3
'------db prepare-----4
Dim conn, vcc, sql5
6
sqlb = "select * from vba_test "7
Set conn = New ADODB.Connection8
conn.Open "driver={sql server};server=(local);UID=mydb;PWD=user; database=pass"9
Set vcc = New ADODB.Recordset10
11
'---- fill------12
Dim msg13
If Application.Documents(1).Tables.Count >= 1 Then14
Dim tb, name, result15
Set tb = Application.Documents(1).Tables(1)16
Dim i17
For i = 1 To tb.Rows.Count18
name = tb.Cell(i, 1).Range.Text19
sql = sqlb & "where name='" & Left(name, Len(name) - 2) & "'"20
vcc.Open sql, conn, 3, 221
' MsgBox vcc = Null22
If Not vcc.EOF Then23
result = vcc("info") & " "24
tb.Cell(i, 2).Range.Text = result25
End If26
vcc.Close27
Next28
29
Set tb = Nothing30
End If31
32
''--------clean up33
Set conn = Nothing34
Set vcc = Nothing35
End Sub36
37
下面是截图
