VBA ADODB excel - 从记录集中读取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3955061/
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
VBA ADODB excel - read data from Recordset
提问by Gadolin
Hope you can help me, I would like to read data from excel file, and the way I was doing was creating instance of Excel application in backgroud, but than I am prompted about VBA macros - disable or enable it.
希望您能帮助我，我想从 excel 文件中读取数据，而我的做法是在后台创建 Excel 应用程序的实例，但系统提示我有关 VBA 宏的信息 - 禁用或启用它。
I have 100 of excel files that I need collect data from, so if I would be prompted every single file, i would end up with really not effective approach. Though I am newbie in Excel VBA world and starting to doubt if there is any other way.....
我有 100 个需要从中收集数据的 excel 文件，所以如果每个文件都提示我，我最终会得到非常无效的方法。虽然我是 Excel VBA 世界的新手，并开始怀疑是否还有其他方法......
My question is can I open them in other way?
我的问题是我可以用其他方式打开它们吗？
I find sth ADODB, I feel this might help me. So I have code as below. As first thing I would like to read data from few cells. I have no idea how can I read the data. I try to read as you can seen below but it throws bug. Opennig connection goes well, query execution also. But then I just guess, how to read the data.
我找到了 ADODB，我觉得这可能对我有帮助。所以我有如下代码。首先，我想从几个单元格中读取数据。我不知道如何读取数据。我试着像你在下面看到的那样阅读，但它会引发错误。Opennig 连接顺利，查询执行也顺利。但后来我只是猜测，如何读取数据。
I use VBA editor.
我使用 VBA 编辑器。
Sub hello_jet()
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=D:\test.xls" & _
"Extended Properties=Excel 8.0;"
.Open
End With
strQuery = "SELECT * FROM [Sheet1$E36:E38]"
Set rs = cn.Execute(strQuery)
Do While Not rs.EOF
Set strNaam = rs.Fields(0).Value
Loop
rs.Close
End Sub
I am working in Office 2003. Yet more I find out that version of excell should be 11. This does not work
我在 Office 2003 中工作。但更多的是我发现 Excel 的版本应该是 11。这不起作用
回答by Fionnuala
I am surprised that the connection string works for you, because it is missing a semi-colon. Set is only used with objects, so you would not say Set strNaam.
我很惊讶连接字符串对您有用，因为它缺少分号。Set 仅用于对象，因此您不会说 Set strNaam。
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=D:\test.xls " & _
";Extended Properties=""Excel 8.0;HDR=Yes;"""
.Open
End With
strQuery = "SELECT * FROM [Sheet1$E36:E38]"
Set rs = cn.Execute(strQuery)
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(i).Name, rs.Fields(i).Value
strNaam = rs.Fields(0).Value
Next
rs.MoveNext
Loop
rs.Close
There are other ways, depending on what you want to do, such as GetString (GetString Method Description).
还有其他方法，具体取决于您要执行的操作，例如 GetString ( GetString Method Description)。
