VBA ADODB excel - read data from Recordset

Question

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

Answer 1

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 (GetString Method Description).