EXCEL-Accept input from cell in SQL query

[Nakis]

Hi there,
please help with the following so that I avoid writing multiple queries...

I use "Get External Data" to get data from Oracle DB table.
Let's say I have the query:
SELECT * FROM TABLE1 WHERE COLUMN1='STR1';

Can I put value STR1 into a cell of my worksheet, so that the query, in general, reads whatever value is in there?
If yes, how should the SQL query look like?

thanx a million.

 

[ETID]

I'm not sure what you mean....do you to extract a single field from a single record and then populate a cell in excel with that value?

 

[Nakis]

No no,
I can extract what I want, this is not a problem...
In my SQL statement I say " where COLUMN1='STR1' " ... well I want to be able to pass STR1 (or any other string, as a parameter from a cell.
For example, I want to be able to put STR1 into cell A1 and my SQL statement to read from that cell.
The statement should have the meaning of
"where COLUMN1='value in cell A1' "

Thanx for looking into it.

 

[ETID]

Are you using MsQuery to make the connection to your DB?

 

[Nakis]

Yes, I use MS Query.
I found a add-in tool that can do that called excelsql (

http://www.excelsql.com)

This add-in allows SQL statements to contain variables from other parts of excel the sheet.

That's exactly what I need to do, but I just don't know if I can do it through MS-Query and if yes, how !

 

[ETID]

Simply record as a macro, the entire process the entrire process that returns your data...Data>Get External data>...etc.

then when data is returned....stop the macro.... then alt+F11 to edit the SQL statement that was recorded.

You will be able to concatenate....your strings into the statement.

Something like this

DoCcmd runsql "select * from my_table where my_field ="&str$(sheets("your sheet here&quot.range("your cell ref.))