I cant found parameters [Solved]

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.80 out of 5)
Go back to All Questions Login or Register
Solved359 views

Hi. How can use parameter for SQL Server conn. like (?)


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:

With Selection.ListObject.QueryTable
.Connection = _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=XXXXXX;Extended Properties="""""
.CommandType = xlCmdSql
.CommandText = Array( _
.Refresh BackgroundQuery:=False
End With

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.

Simply the best place to learn VBA!