Passing Cell values to a SQL statement

[Tyler WIllis]

I have set up a spreadsheet that uses excel's "Get external data"
feature to connect to a SQL database and run a querry. The problem is
that I need to get data for several different product codes. These
codes change from time to time but basically the querry looks like
this

Select * from SalesData
Where
SalesData.productCode = 1
Or
SalesData.productCode = 2

is there a way to set it up such that the product codes are passed to
the statement from cell values in a given range? This way it would
dynamically retrive whatever I am looking for.

Also if any of you double as SQL wizzards, can I pass an array of
values into my Where clause so that I don't have to list each
sepperately?

Thanks.

Tyler

 

[Arvi Laanemets]

Hi

You need a procedure which will rewrite the CommandText propertie for querie
object. Here is an example
Public Sub UuendaPäringud()
kuu = ActiveSheet.Range("F1").Value
aasta = ActiveSheet.Range("F2").Value
...
Set qtQtrResults = Worksheets("Tasud").QueryTables(1)
Sheets("Tasud").Activate
ActiveSheet.Range("A2").Select
With qtQtrResults
.CommandType = xlCmdSql
.CommandText = _
"SELECT a.tabn, SUM(a.summa) AS arvest, "&_
"SUM(IIF(a.sotsmaks,1,0)*a.summa) AS sotsalus, "&_
"a.allyksus FROM tasud a "&_
"WHERE a.tabn<>'' AND a.summa>0 AND a.tl<'060' AND "&_
"Year(a.sisestus)=" & aasta & " AND "&_
"Month(a.sisestus)=" & kuu & " "&_
"GROUP BY a.allyksus, a.tabn ORDER BY a.tabn"
.Refresh
End With
.....
End Sub

 

[onedaywhen]

You can use the IN keyword i.e.

Select * from SalesData
Where SalesData.productCode IN(1,2)

Performace will depending on which DBMS you are using, so do some
testing if you intend to use

Again depending on DBMS product, it is preferable to create a stored
procedure on the server side and get the client (Excel) to pass the
values for the IN clause as parameters (might get away with passing as
a single string paramter?)

 

[Tyler WIllis]

Thanks, Exactly the sort of answers I was looking for. Sorry for not
getting this into the right section. I didn't notice the ADO thread
till after I posted.