How to retrieve data from Excel with ADODB connection if the first line of the worksheet does not have the column name?

excel vba adodb
kb_sou picture kb_sou · Oct 19, 2012 · Viewed 31.3k times · Source

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

Doug Glancy picture Doug Glancy · Oct 19, 2012

See this Microsoft page. You can use something like:

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

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
Excel VBA - connect to sql with a trusted connection (no uid/pwd)

Is there a way to have Excel connect to Sql Server using the current user's network windows authentication (trusted connection/integrated security)? I don't want to rely on a sql login or have a uid/password in my code.

Read More
VBA New Database Connection

How to change the code below to prevent what you see in the screenshot. I am running a macro with the following code Dim conn As ADODB.Connection Dim rec1 As ADODB.Recordset Dim thisSql As String Set conn = New …

Read More