SQL queries with Excel in VBA to populate a spreadsheet

[Franck]

Hi,

Can somebody help me on the following point?

I want to use ODBC connection to another Excel file and
set up an SQL request with a QueryTable object.
The result to filter depends on IDs given by the user
in one cell of the workbook. So far so good.

My problem is that I do not know how to format the SQL
request nor write the corresponding VBA code to SQL
request several ID at the same time (outcomes as an union
of up to three IDs).
What to change to the following code?

rq = "Select * from `Table$` Where (CRD_ID=?)"
Me.QueryTables(1).Parameters.Add( "CRD_ID", xl...)
Me.QueryTables(1).Parameters(1).SetParam xlRange, Range
("A1")

Thanks a lot.

 

[Patrick Molloy]

Just build th esql query
Sql = "Select [field1],[Field2] from mydata where" & _
"[field1]='" & test1 & "', and [Field2]=" & test2
& ";"

note that test1 is textual - hence the single quote
bracing it and i've let test2 be numeric

see
http://www.xl-
expert.com/html_pages/dataConnectivity_Excel.html

Patrick Molloy
Microsoft Execl MVP