excel query table with SQL IN() multiple value parameter

[emosms]

Hi, I am looking for a way to insert a single column range as a parameter to an excel query table parameter.
I know, the ? mark is a parameter and it prompts u to select a cell where the parameter value is.
---
Tried to set a formated string into a cell, like 'param1', 'param2' , "'param', 'param2'" (not sure about the single quoute and escape characters) but it did not work. Seems that the question mark holds single value only, not an array of values or formated string with multiple values and commas.
---
If i set it like this: ...WHERE col_name IN (?,?,?) it woks. If I hardcode the values into the sql IN() statement, it works.
Even if I write a nested sql query returning a single value it works: ...WHERE col_name IN(SELECT col_blabla from tbl_blabla...)
---
The most convenient way for my daily work would be that I select a range of values and the values being inserted as an array of values param.
How can I achieve this?
Not that fresh these days, could not figure it out by myself.
---
I remember I tried to obtain the query table object by VBA, I got the query table, but the command text property is read only and I cannot manipulate the sql string.
Can I set a paramter variable somewhere in the query and dynamically set it?
(being a formatted string with multiple values, array, collection, whatever..)
The number of values will vary, depending on what I want to query.
p.s.
the query tables retrieve data from SQL server, but I would like the solution to work with ms access database as well.

Kind Regards and Thx

 

[emosms]

I worked out a pretty simple solution. Actually one can alter the SQL 'command text':

Function tbl_get()
Dim conn As WorkbookConnection
Dim sql As String
sql = "SELECT Guests.name, Guests.addr FROM guests"
Set conn = ActiveWorkbook.Connections("guests") ' connection name from the properties, right click the query table
conn.ODBCConnection.CommandText = ""
conn.ODBCConnection.CommandText = sql & " WHERE name IN(" & in_string & ")"
conn.Refresh
'Debug.Print conn.ODBCConnection.CommandText
End Function


Function in_string() As String
Dim in_sql As String: in_sql = ""
For Each s In Range("A:A") ' if you pick to copy paste parameter values in the first column
in_sql = in_sql & "'" & s.Value & "'," '& vbCrLf
If s.Value = "" Then
Exit For
End If
Next s
in_sql = Left(in_sql, Len(in_sql) - 4) 'god knows why 4 characters at the end :D
in_string = in_sql
End Function

Sub query()
' a button attached to this
Call tbl_get
End Sub

If could be simplier, ping here :D