Question

EXCEL VB从ACCESS提取数据问题。

Sub PriceQuery()

Dim Cnn
Dim rst
Dim Sql As String
Dim STR
Dim str11 As Range
Dim i As Integer

STR1 = Worksheets("a").Range("a1:a10")

Set Cnn = CreateObject("ADODB.Connection") '创建连接对象

Set rst = CreateObject("ADODB.Recordset") '创建数据集对象

Cnn.Open "Provider=Microsoft.Ace.OleDB.12.0;data Source=d:\学习.accdb" '打开数据源为access

For Each STR In STR1

i = 1

STR = Worksheets("a").Cells(i, 1)

Sql = "select * from [TABLE1] where 姓名 like '%" & STR & "%'"

Sheets("a").Cells(i, 2).CopyFromRecordset Cnn.Execute(Sql)

i = i + 1

Next STR

Cnn.Close '关闭连接，释放资源
Set Cnn = Nothing '关闭连接，释放资源

End Sub

Answer 1

STR1 = Worksheets("a").Range("a1:a10")
改为：
SET STR1 = Worksheets("a").Range("a1:a10")

Answer 2

啥问题，说明白了！