|Go back to All Questions||Login or Register|
I don’t believe you can use ? in MS Excel when using SQL Connections. I think the query needs to be static.
However a Workaround
1. Create your SQL Query first (Data->New Query->From Database.
2. Then when the Query is created (as a Table in Excel Worksheet) simply edit the Query with VBA:
.Connection = _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=XXXXXX;Extended Properties="""""
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM MYTABLE WHERE ID = XX")
Where the SELECT statement can be any new valid SQL statement – one that includes yours new params (such as ID = XX). You can change it as you like to Add parameters.
This workaround will require running the Macro each time but then again you would anyway need to refresh the table so I believe it is a good option to go for.