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 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)