1

I'm running MySQL queries in Excel through an ODBC connection.

So far, no problem using parameters, putting an ? in my queries, and when prompted, selecting cells C2, and I get the result in A4 as expected:

SELECT id_product FROM ps_product WHERE id_product = ? LIMIT 100;

So far so good using id_product = ? and when prompted selection C2

Thing is, I want to be able to run the query using a cell value as a string as a parameter, so doing something like:

SELECT id_product FROM ps_product WHERE id_product IN (?) LIMIT 100;

When Excel prompts me which cell to use as a parameter, I select the cell C2 that I changed here, and it still only returns in A4 the first ID, not the second one:

Still first id only

Any idea of what I should do to make this work, so in A4 A5 I have both product IDs showing?

I've looked all around, used simple and double quotes in C2 or directly in the query, played with parenthesis, nothing seems to work...

Many thanks!

Improve this question

1 Answer 1

Reset to default
1

in this situation you are attempting to pass a list into the cell to be read in your SQL query. Unfortunately, this is not possible by dynamically selecting the cell using the ? in your query in excel. There are a few posts about this and possible workarounds like this one involving VBA.

Parameterized query from Excel with IN clause

However, if you are able to work VBA, I would suggest creating a sub that would run your query and dynamically pick up ranges on a worksheet. For example,

Sub query()
    Sql = "SELECT id_product FROM ps_product WHERE id_product in '" & Range("C2") & "' AND '" & Range("D4") & "' "

    With ThisWorkbook.Connections("Your Connection Name Here").ODBCConnection
        .CommandText = Sql
        .Refresh
    End With
end sub

Of course, the idea extends if you have more IDs to enter.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.