Excel VBA - 如何从 SQL 查询填充数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8086410/
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
Excel VBA - How to Populate Array from SQL Query
提问by AFJ
I've been trying to write a piece of code that would allow me to query an SQL DB and use the returned values to populate a combobox.
我一直在尝试编写一段代码，让我可以查询 SQL 数据库并使用返回的值来填充组合框。
The query runs fine when I ask it to return the values to the worksheet, however I don't want them there, I simply want to store them in an array to be used by the combobox.
当我要求它将值返回到工作表时，查询运行良好，但是我不希望它们在那里，我只是想将它们存储在组合框使用的数组中。
Here's what I have so far...
这是我到目前为止...
Sub testQuery()
Dim varConn As String
Dim SQL As String
Dim test As String
Range("A1").CurrentRegion.ClearContents
varConn = "ODBC; DSN=Traceability DB;UID=XXX;PWD=XXX"
SQL = "Select Distinct ""Date"" from testtable"
With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"), SQL:=SQL)
.Refresh
End With
UserForm1.Show
End Sub
I'm not sure how to replace Range("A1") to an array.
我不确定如何将 Range("A1") 替换为数组。
Note: this is being used on the latest version of Excel for the MAC.
注意：这是用于 MAC 的最新版本的 Excel。
Thanks for the help
谢谢您的帮助
回答by user194076
Save it in record set. Something like this:
将其保存在记录集中。像这样的东西：
Set rs = db.OpenRecordset("Select Distinct ""Date"" from testtable")
To access records in record set use GetRowsSomething like this:
要访问记录集中的记录，请使用GetRows以下内容：
data = rs.GetRows(j)
and then loop through data.
然后遍历数据。
