| Author |
Variable SQL Statements pulling from a cell in Excel
|
|
| Preacher Man 2006-03-22, 1:24 pm |
| I have a situation that I hope someone can give me a good answer to. Please
bear with me as I try to explain my needs.
My goal is to have an Excel spreadsheet that pulls from a SQL database and
the the query pulls from a variable in the Excel sheet. For example let's
say I am pulling Sales Orders from SQL, let's also simply assume the record
has two fields Year and SOno.
Under a simple query I could say Select * from table. Of course that would
pull all so's from every year.
To filter the query of course I could say Select * from table where
year='2006'. And naturally that would give me 2006 orders.
OK, here's where I can't figure this out. I want the query to filter
according to a value in a cell in excel. Let's use cell A1. Essentially I
am looking for a query that says "Select * from table where Year=(Cell A1 in
the Sheet)"
Is this possible?
| |
| Ardus Petus 2006-03-22, 1:24 pm |
| Use :
SELECT * FROM TABLE WHERE YEAR = ?
Close Microsoft Query and get back to Excel
In popup window, select Parameters...
HTH
--
AP
"Preacher Man" <nospam> a écrit dans le message de
news:OlWApedTGHA.776@TK2MSFTNGP09.phx.gbl...
> I have a situation that I hope someone can give me a good answer to.
Please
> bear with me as I try to explain my needs.
>
> My goal is to have an Excel spreadsheet that pulls from a SQL database and
> the the query pulls from a variable in the Excel sheet. For example let's
> say I am pulling Sales Orders from SQL, let's also simply assume the
record
> has two fields Year and SOno.
>
> Under a simple query I could say Select * from table. Of course that
would
> pull all so's from every year.
>
> To filter the query of course I could say Select * from table where
> year='2006'. And naturally that would give me 2006 orders.
>
> OK, here's where I can't figure this out. I want the query to filter
> according to a value in a cell in excel. Let's use cell A1. Essentially
I
> am looking for a query that says "Select * from table where Year=(Cell A1
in
> the Sheet)"
>
> Is this possible?
>
>
| |
|
| Write a Macro in the Excel Spreadsheet which will take the cell parameters.
[A1.YEAR] which can be passed to the query.
"Preacher Man" wrote:
> I have a situation that I hope someone can give me a good answer to. Please
> bear with me as I try to explain my needs.
>
> My goal is to have an Excel spreadsheet that pulls from a SQL database and
> the the query pulls from a variable in the Excel sheet. For example let's
> say I am pulling Sales Orders from SQL, let's also simply assume the record
> has two fields Year and SOno.
>
> Under a simple query I could say Select * from table. Of course that would
> pull all so's from every year.
>
> To filter the query of course I could say Select * from table where
> year='2006'. And naturally that would give me 2006 orders.
>
> OK, here's where I can't figure this out. I want the query to filter
> according to a value in a cell in excel. Let's use cell A1. Essentially I
> am looking for a query that says "Select * from table where Year=(Cell A1 in
> the Sheet)"
>
> Is this possible?
>
>
>
| |
| Preacher Man 2006-03-22, 8:24 pm |
| Could you please elaborate on the steps.
Thanks.
"Dan" <Dan@discussions.microsoft.com> wrote in message
news:90A7A930-720A-48AC-91EA- ADF6B21DFF2D@microso
ft.com...[color=darkred]
> Write a Macro in the Excel Spreadsheet which will take the cell
> parameters.
> [A1.YEAR] which can be passed to the query.
>
> "Preacher Man" wrote:
>
| |
| Steen Persson (DK) 2006-03-28, 7:23 am |
| Preacher Man wrote:
> Could you please elaborate on the steps.
>
> Thanks.
Hi
I don't know if you already has got it working, but something like the
code below will do the trick.
Range("A7").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DRIVER=SQL
Server;SERVER=YourSe
rverName;UID=UserNam
e;APP=Microsoft Office
2003;WSID=xxxxx;DATA
BASE=YourDatabaseNam
e;Network=DBMSSOCN;T
rusted_Connection="
_
), Array("Yes"))
.CommandText = Array("EXEC SomeStoredProcedure " +
CStr(Range("A2")) + "," + CStr(Range("A3")))
.Refresh BackgroundQuery:=Fal
se
The above it done in a macro, and then I have a commandbutton that
executes the macro.
The first line "Range("A7").select tells where to start inserting the
result into the Excel sheet. The Range("A3") and Range("A2") paramters
tells which cell to get the values from. The above example is based on
running a stored procedure that takes 2 parameters but if you get the
idea I'm sure you can adjust it to your needs. The Cstr(....) is a
conversion to a string which I had to have to get it running.
I'm not at all an expert on Excel and/or VB so the code can most likely
be nicer...;-).
Regards
Steen
|
|
|
|