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.

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.

"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.

--