VBA ADODB excel - 从Recordset读取数据 [英] VBA ADODB excel - read data from Recordset

问题描述

希望你可以帮助我，我想从excel文件中读取数据，而我正在做的是
的方式是在backgroundud中创建Excel应用程序的实例，但比
提示有关VBA宏 - 禁用或启用它。

我有100个Excel文件，我需要收集数据，所以如果我将被提示每一个文件，我会最终真的没有效果的方法虽然我是Excel VBA世界中的新手，开始怀疑是否有其他方式.....

我的问题是我可以以其他方式打开吗？ / p>

我发现std ADODB，我觉得这可能会帮助我。所以我有如下代码。
首先我想从几个单元读取数据。我不知道如何读取数据
。我尝试阅读，你可以看到下面，但它会抛出错误。 Opennig连接顺利，查询执行也很好。但是我只是猜测，如何读取数据。

我使用VBA编辑器。

  Sub hello_jet（）
设置cn = CreateObject（ADODB.Connection）
带有cn 
 .Provider =Microsoft.Jet.OLEDB.4.0
 .ConnectionString =Data Source = D：\test.xls& _ 
扩展属性= Excel 8.0; 
 .Open 
结束
 strQuery =SELECT * FROM [Sheet1 $ E36：E38]
设置rs = cn.Execute（strQuery）
尽管没有rs.EOF 
设置strNaam = rs.Fields（0）.Value 
循环
 rs.Close 
 End Sub 
  

我正在Office 2003中工作。而且我发现excell的版本应该是11.这不工作

解决方案

我很惊讶连接字符串对你有用，因为它缺少分号。
Set只用于对象，所以你不会说Set strNaam。

 设置cn = CreateObject（ADODB $ c 
 .Provider =Microsoft.Jet.OLEDB.4.0
 .ConnectionString =Data Source = D：\test.xls& _ 
;扩展属性=Excel 8.0; HDR =是;
 .Open 
结束
 strQuery =SELECT * FROM [Sheet1 $ E36：E38 ]
设置rs = cn.Execute（strQuery）
尽管不是rs.EOF 
对于i = 0到rs.Fields.Count  -  1 
 Debug.Print rs。字段（i）.Name，rs.Fields（i）.Value 
 strNaam = rs.Fields（0）.Value 
下一个
 rs.MoveNext 
循环
 rs.Close 
  

有其他的方法，取决于你想要做的，如GetString（< a href =http://www.w3schools.com/ado/met_rs_getstring.asp =noreferrer> http://www.w3schools.com/ado/met_rs_getstring.asp ）。

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.

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.....

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.

I use VBA editor.

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

解决方案

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 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 (http://www.w3schools.com/ado/met_rs_getstring.asp).

这篇关于VBA ADODB excel - 从Recordset读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！