using SQL to query Excel (or Access?) selecting specific row numbe
 

Using the ODBC connections to query Excel, can one get rows by row number?
for example get rows 10 - 20 in an excel spreadsheet (and while I'm at it,
perhaps specify that the data on rows 10 are to be column headers).
Sounds easy, but can't seem to find a way.

using SQL to query Excel (or Access?) selecting specific row numbe
 

See destination option below

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\PMI Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), Array("=5;")), Destination:=Range("A1"))

"KingGeezer" wrote:

Using the ODBC connections to query Excel, can one get rows by row number?
for example get rows 10 - 20 in an excel spreadsheet (and while I'm at it,
perhaps specify that the data on rows 10 are to be column headers).
Sounds easy, but can't seem to find a way.

using SQL to query Excel (or Access?) selecting specific row n
 

Thanks Joel!
Just to make sure I'm going in the right direction, is the 'Destination
Range' something that refers to the Excel sheet that I'm querying, or to the
table I'm bringing the data back into?
In your example, the Destination was A1; if I wanted to retrieve data
starting on the 10th row of an Excel spreadsheet, would I use A10 as the
Destination?
Then how would I specify only 10 rows to be read in?
What I'm fearing is that A1 in your example specifies where to 'put' the
data in the current table once its read, but maybe I'm wrong; I haven't used
the Destination option before.

"Joel" wrote:

See destination option below

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\PMI Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), Array("=5;")), Destination:=Range("A1"))

"KingGeezer" wrote:

Using the ODBC connections to query Excel, can one get rows by row number?
for example get rows 10 - 20 in an excel spreadsheet (and while I'm at it,
perhaps specify that the data on rows 10 are to be column headers).
Sounds easy, but can't seem to find a way.

using SQL to query Excel (or Access?) selecting specific row n
 

Destinationis the worksheet in excel starting location. It is MAXRECORDS
(ignore blanks rows I added below)

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\PMI Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _

-----------------------------------------------------------
"MaxRecords=10;" & _

--------------------------------------------------------------
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), Array("=5;")), Destination:=Range("A1"))


"KingGeezer" wrote:

Thanks Joel!
Just to make sure I'm going in the right direction, is the 'Destination
Range' something that refers to the Excel sheet that I'm querying, or to the
table I'm bringing the data back into?
In your example, the Destination was A1; if I wanted to retrieve data
starting on the 10th row of an Excel spreadsheet, would I use A10 as the
Destination?
Then how would I specify only 10 rows to be read in?
What I'm fearing is that A1 in your example specifies where to 'put' the
data in the current table once its read, but maybe I'm wrong; I haven't used
the Destination option before.

"Joel" wrote:

See destination option below

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\PMI Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), Array("=5;")), Destination:=Range("A1"))

"KingGeezer" wrote:

Using the ODBC connections to query Excel, can one get rows by row number?
for example get rows 10 - 20 in an excel spreadsheet (and while I'm at it,
perhaps specify that the data on rows 10 are to be column headers).
Sounds easy, but can't seem to find a way.