Bruno Lopes Bruno Lopes - 2 months ago 20
SQL Question

Insert value of Excel cell as a SQL query parameter in macro

I want to pass the value of an Excel cell into a query inside a macro, so that when I insert the value in that specific cell, and press Enter, it triggers the macro and the query inside it.

Let's say that cell is "A1" and the value i will pass as parameter (this time) is "99"

If i do this "manually", the query will have the parameter inserted on it in the following way:

Set rs = conn.Execute("SELECT * FROM CODES WHERE ref_id like '99%' ")


What modifications are necessary so the query accepts any value from A1 cell?

Answer

What I use in an Excel based SQL download, is that I've put the script in A:A and then use this VBA code:

Range("A1").Select
'Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
    ' Concatenate using a space after each line
        sql = sql + ActiveCell.Value + " "

    ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
Loop

So it will read it all, line-by-line.

In your case, I think this should be it:

Range("A1").Select
        sql = "SELECT * FROM CODES WHERE ref_id like '" + ActiveCell.Value + "%' "
Set rs = conn.Execute(sql)