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

User · Answer

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

[excel] VBA ADODB excel - read data from Recordset

Examples related to excel

Examples related to vba