作者:年庚瑶 | 来源:互联网 | 2023-07-18 09:41
先在在工具栏右键选控件工具箱,再插入一个按钮来触发VBA的函数,双击按钮,进行代码编辑,在“工具”->“引用”添加MicrosoftActiveXData2.8library和下面的Mi
先在在工具栏右键选控件工具箱,再插入一个按钮来触发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
35
End Sub
36
37
下面是截图