VBA连接EXcel数据库(非引用Excel)

caiqs

Public conn As New ADODB.Connection   '定义新连接
Public cmd As New ADODB.Command     '定义指令
Public rs As New ADODB.Recordset  '定义两个记录集

'VBA连接Excel数据库
Sub aa()
Set conn = New Connection
conn.Open "Provider=msdasql.1;DBQ=D:\1.xls;Driver={Driver do Microsoft Excel(*.xls)};ReadOnly=1"
Set cmd.ActiveConnection = conn
cmd.CommandText = "select * from AA"
rs.CursorLocation = adUseClient

'为什么下边这行会出错,换成连接access时,同样的语法可以通过

rs.Open cmd, , adOpenStatic, adLockBatchOptimistic

Dim num As Integer
num = rs.Fields.Count
'声明动态数据,存储所有字段名
Dim a()
Dim n As Integer
ReDim a(num - 1)
n = 0
For Each Red In rs.Fields
a(n) = Red.Name
n = n + 1
Next
cc = rs.GetRows(, 2, "半径")
rs.Close
End Sub