How to add parameters to an external data query in Excel which can't be displayed graphically?

Excel's interface for SQL Server queries will not let you have a custom parameters.  A way around this is to create a generic Microsoft Query, then add parameters, then paste your parametorized query in the connection's properties.  Here are the detailed steps for Excel 2010:

1. Open Excel
2. Goto Data tab
3. From the From Other Sources button choose From Microsoft Query
4. The "Choose Data Source" window will appear.  Choose a datasource and click OK.
5. The Query Qizard
1. Choose Column: window will appear.  The goal is to create a generic query. I recommend choosing one column from a small table.
2. Filter Data: Just click Next
3. Sort Order: Just click Next
4. Finish: Just click Finish.
6. The "Import Data" window will appear:
1. Click the Properties... button.
1. Choose the Definition tab
2. In the "Command text:" section add a WHERE clause that includes Excel parameters.  It's important to add all the parameters that you want now.  For example, if I want two parameters I could add this:
   WHERE 1 = ? and 2 = ?
3. Click OK to get back to the "Import Data" window
2. Choose PivotTable Report
3. Click OK
7. You will be prompted to enter the parameters value for each parameter.
8. Once you have enter the parameters you will be at your pivot table
9. Go batck to the Data tab and click the connections Properties button
1. Click the Definition tab
2. In the "Command text:" section, Paste in the real SQL Query that you want with the same number of parameters that you defined earlier.
3. Click the Parameters... button 
1. enter the Prompt values for each parameter
2. Click OK
4. Click OK to close the properties window
10. Congratulations, you now have parameters.

Easy Workaround (no VBA required)

1. Right Click Table, expand "Table" context manu, select "External Data Properties"
2. Click button "Connection Properties" (labelled in tooltip only)
3. Go-to Tab "Definition"

From here, edit the SQL directly by adding '?' wherever you want a parameter. Works the same way as before except you don't get nagged.

If you have Excel 2007 you can write VBA to alter the connections (i.e. the external data queries) in a workbook and update the CommandText property. If you simply add ? where you want a parameter, then next time you refresh the data it'll prompt for the values for the connections! magic. When you look at the properties of the Connection the Parameters button will now be active and useable as normal.

E.g. I'd write a macro, step through it in the debugger, and make it set the CommandText appropriately. Once you've done this you can remove the macro - it's just a means to update the query.

Sub UpdateQuery    Dim cn As WorkbookConnection    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection    For Each cn In ThisWorkbook.Connections        If cn.Type = xlConnectionTypeODBC Then            Set odbcCn = cn.ODBCConnection            ' If you do have multiple connections you would want to modify              ' the line below each time you run through the loop.            odbcCn.CommandText = "select blah from someTable where blah like ?"        ElseIf cn.Type = xlConnectionTypeOLEDB Then            Set oledbCn = cn.OLEDBConnection            oledbCn.CommandText = "select blah from someTable where blah like ?"         End If    NextEnd Sub