sql query for excel column values
 

I have a spreadsheet that I receive daily. there are about anywhere from
50-1000 values in column A that I need to query an sql database for. The
spreadsheet name changes daily and the number of rows change daily. It's
always column A (with a header). I'd like to program excel to start at cell
A2, do the query, return the results to a new workbook, and continue down the
list until the end.

So far I've recorded the macro that sets up the database and returns a
sample query, but I'm not sure how to go about the rest. I know the
variables are the daily file, the number of rows in the dailyfile. How could
I set it up so that it queries each cell value until the end of the data?
Automation is the key here. I'm querying an MS SQL database.

sql query for excel column values
 

Try this:

Sub ReadColumn1()
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Add
Sql = "Select * from [Sheet1$]" ' Replace * by what is in A1
Cnn = "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=#;'"
Cnn = Replace(Cnn, "#", "C:\Book1.xls") ' Use your book name
Set ADORS = CreateObject("ADODB.RecordSet")
ADORS.Open Sql, Cnn
XL.ActiveSheet.Range("A2").CopyFromRecordset ADORS
XL.Visible = True
ADORS.Close
Set ADORS = Nothing
End Sub


"Daniel Seipel" wrote:

I have a spreadsheet that I receive daily. there are about anywhere from
50-1000 values in column A that I need to query an sql database for. The
spreadsheet name changes daily and the number of rows change daily. It's
always column A (with a header). I'd like to program excel to start at cell
A2, do the query, return the results to a new workbook, and continue down the
list until the end.

So far I've recorded the macro that sets up the database and returns a
sample query, but I'm not sure how to go about the rest. I know the
variables are the daily file, the number of rows in the dailyfile. How could
I set it up so that it queries each cell value until the end of the data?
Automation is the key here. I'm querying an MS SQL database.

sql query for excel column values
 

"Daniel Seipel" wrote:

I have a spreadsheet that I receive daily. there are about
anywhere from
50-1000 values in column A that I need to query an sql database
for.The
spreadsheet name changes daily and the number of rows change daily.
It's
always column A (with a header). I'd like to program excel to
start at cell
A2, do the query, return the results to a new workbook, and
continue down the
list until the end.

No need to loop through the values row-by-row. SQL is a set based
language and, using Jet's ability to create a join between a table
(e.g. Excel range) and an odbc table, you can get all the data you need
(no more, no less) in one hit e.g. something like:

SELECT
MSSQL.key_col, MSSQL.data_col
FROM
[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=MYDATABASE;UID=** *;Pwd=***;].MyTable
AS MSSQL
RIGHT JOIN
[Excel 8.0;HDR=NO;Databse=C:\MyWorkbook.xls;].[MySheet$A2:A65535] AS
XL
ON MSSQL.key_col = XL.F1
ORDER BY XL.F1;

Jamie.

--