如何获取访问查询到excel vba作为范围 [英] How to get an access query into excel vba as a range

问题描述

OK，所以我有这个功能，使用我传递给它的查询字符串来查询数据库。
目前它在工作表中输出查询结果。
我如何获取函数给我的结果作为我可以在VBA中使用的范围来执行计算等等？那么我该如何引用这个范围？例如。获取结果中的名称列。

OK so I have this function which queries a database using a query string that I pass to it. At the moment it outputs the query result in the worksheet. How do I just get the function to give me the result as a range that I can use in VBA to perform calculations etc? How would I then reference this range? E.g. to get the "Name" column in the result.

Function Access_Data(query As String)
 'Requires reference to Microsoft ActiveX Data Objects xx Library

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, Col As Long, c As Long
Dim MyField, Location As Range

 'Set destination
Set Location = Sheets(1).Range("a1")
 'Set source
MyConn = "S:\Docs\Harry\Engine Client\Engine3.accdb"
 'Create query
sSQL = query

 'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn
    Set Rs = .Execute(sSQL)
End With

 'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
c = Col
Do Until Rs.EOF
    For Each MyField In Rs.Fields
        Cells(Rw, c) = MyField
        c = c + 1
    Next MyField
    Rs.MoveNext
    Rw = Rw + 1
    c = Col
Loop
Set Location = Nothing
Set Cn = Nothing

结束功能

推荐答案

Function Access_Data(query As String)
 'Requires reference to Microsoft ActiveX Data Objects xx Library

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, c As Long
Dim MyField, Result

 'Set source
MyConn = "S:\Docs\Harry\Engine Client\Engine3.accdb"
 'Create query
sSQL = query

 'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .CursorLocation = adUseClient
    .Open MyConn
    Set Rs = .Execute(sSQL)
End With

 'Write RecordSet to results
Redim Result(1 To Rs.RecordCount, 1 To Rs.Fields.Count)
Rw = 1
Do Until Rs.EOF
    c = 1
    For Each MyField In Rs.Fields
        Result(Rw, c) = MyField
        c = c + 1
    Next MyField
    Rs.MoveNext
    Rw = Rw + 1
Loop
Set Cn = Nothing

Access_Data = Result
End Function

这将返回一个多维数组。范围必须引用工作表的某些部分：您不能创建隐形范围。 （虽然你可以使工作表的一部分看不见，如果这是你以后的）。

This will return a multi-dimension array. A range must refer to some portion of a worksheet: you cannot create an "invisible" range. (Though you can make a portion of a worksheet invisible, if that's what you're after.)

要访问结果：

Dim v, i As Long
v = Access_Data("select ID, Name from somewhere")
For i = 1 To UBound(v, 1)
    MsgBox v(i, 1) & " / " & v(i, 2)
Next

这篇关于如何获取访问查询到excel vba作为范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！