Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
Run Access query from Excel, input value to query being value in c | Excel Programming | |||
Access query to specific cells | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Can You Query Specific Access Records from Excel? | Excel Programming |