sql query for excel column values

[Guest]

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.

 

[Guest]

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

 

[Jamie Collins]

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.

--

 

[Guest]

Those are both great suggestions, and I apprectiate it. However, the thing
is that the name of the daily file changes daily and both suggestions require
me to manually change the code daily. The other issue is, with the first
response, replacing what is in column A defeats the purpose of the
automation. As I said, the number of rows change daily, the filename
changes daily.

My query needs to run based upon weather or not there is an "x" in column
AA2. If there is no x in that column, then I don't need to query what is in
column A. Here is an example:

A AA2
12345 x
54321
23154 x
11111 x

the code shoud start at column A, the check to see if the x is present in
AA2. Or what I could do is sort the column first and then run the query
based something like
do until AA2=""

Sorry for the confusion.

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.

 

[Guest]

That's good except that what is in column A is a variable number of values.
In addition, I only need to query if column AA has a value of "x". Here is
an example

A ... AA (column)
ID ... xcolumn (Header name)
12345 x
11111
22222 x
33333 x

I need to query sql only if the value in AA2 is "x". And the name of the
worksheet changes everyday, as do the number of rows.

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

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.

 

[Jamie Collins]

My query needs to run based upon weather or not there is an "x" in column
AA2.

Change the table to include the column AA2 e.g.

[MySheet$A2:AA65535]

and add an appropriate WHERE clause e.g. could be one of these:

WHERE F27 = 'x'
WHERE F27 IS NOT NULL
WHERE LEN(F27) > 0

Note that the '27' in the column name is the ordinal number of column
AA, not a cell reference.

name of the daily file changes daily and both suggestions require
me to manually change the code daily

You will probably have to deal with the issue of your changing filename
with any VBA solution. Does your daily filename have a pattern e.g.
uses the current date, an incrementing number, etc? If not, how will
your find out which file to use e.g. there's only ever one in the
folder, throw up a dialog for the user to choose a file, etc? Once you
have determined the filename, you can pass it to the procedure as a VBA
parameter e.g. air/pseudo code:

Public Sub Main()
Dim strFilename As String
strFilename = frmFilePicker.Filename
GetData strFilename
End Sub

Private Function GetData( _
ByVal Filename As String _
) As Boolean
Const SQL As String = _
"SELECT * FROM" & _
" [Excel 8.0;Database=<<FILENAME>>;]"
".[MySheet$];"
strSql As String
strSql = Replace(SQL, "<<FILENAME>>", Filename)
m_Connection.Execute strSQL
End Function

Jamie.

--