How to retrieve data from Excel with ADODB connect

Question

I use the following type of code to retrieve data from some Excel Workbooks (path is a Parameter)

Dim strSQL  As String, conStr as String
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset


conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & path & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"

strSQL = "SELECT [Field1], [Field2] FROM [Worksheet$] WHERE [Thing1] > 1"

cnn.open conStr   
rs.Open query, cnn, adOpenStatic, adLockOptimistic, adCmdText

That code works fine if the names of the fields are on the first row of the worksheet. The problem is that I need to retrieve data from a worksheet that the data table begins on another row (Row 10).

Is there a way to specify the first row of my data table?

Answer 1

Use a named or unnamed range in your query:

strQuery = "SELECT * FROM MyRange"

strQuery = "SELECT * FROM [Sheet1$A1:B10]"

See these Microsoft support articles for more information:

How To Use ADO with Excel Data from Visual Basic or VBA

ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

Answer 2

You can query a range of cells starting from row 10:

 "SELECT * FROM [Worksheet$A10:S100] WHERE [Thing1] > 1"

What can be tough is finding what the end of the range should be. You could put in a ridiculously large number, but then you'd have to add special handling for the rows of NULL at the end.

Answer 3

See this Microsoft page. You can use something like:

strSQL = "SELECT [Field1], [Field2] FROM [Worksheet$$A10:B43] WHERE [Thing1] > 1"