如何将访问查询作为范围进入 excel vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12857795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get an access query into excel vba as a range
提问by harryg
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.
好的，所以我有这个函数，它使用我传递给它的查询字符串查询数据库。目前它在工作表中输出查询结果。我如何才能获得函数以将结果作为我可以在 VBA 中用于执行计算等的范围？那么我将如何引用这个范围？例如，在结果中获取“名称”列。
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
End Function
结束函数
采纳答案by tyomitch
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.)
这将返回一个多维数组。范围必须引用工作表的某个部分：您不能创建“不可见”范围。（尽管您可以使工作表的一部分不可见，如果这是您所追求的。）
To access the results:
要访问结果：
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
回答by dee
Sample how to read data from SQL Server and the result insert into worksheet (here using integrated security). If the target sheet was empty before inserting new data then use UsedRange property to reference it. Or calculate it, rng is the top most left cell.
示例如何从 SQL Server 读取数据并将结果插入到工作表中（此处使用集成安全性）。如果在插入新数据之前目标工作表为空，则使用 UsedRange 属性来引用它。或者计算一下，rng是最左上角的单元格。
Option Explicit
' Add reference to Microsoft ActiveX Data Objects Lib
Public Sub main(): On Error GoTo Err_handler
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=MYSUPERSERVER;Initial Catalog=MYSUPERDATABASE;Integrated Security=sspi"
cn.Open
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Open "SELECT * FROM MyTable"
Dim fld As ADODB.Field
Dim rng As Range
Set rng = [a1]
For Each fld In rs.Fields
rng.Value = fld.Name
Set rng = rng.Offset(0, 1)
Next fld
Set rng = rng.Offset(1, -rs.Fields.Count)
rng.CopyFromRecordset rs
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
Err_handler:
MsgBox Err.Description
End Sub
