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

Question

I often use MS Excel's Get External Data to create simple reports - running queries against databases and displaying nicely in Excel. Excel's great features like filtering and pivot tables and familiar interface for users make it quite good for this. However, one limitation with Microsoft Query is you can't add parameters to queries that can't be displayed graphically, which considerably limits the SQL you can write.

Is there any solution to the error "parameters are not allowed in queries that can't be displayed graphically"?

Answer 1

For Excel 2013 button "Parameters", in connection dialog, remains disabled even if the query text contains parameters like "?".

Insert parameters into query text like this:

declare @sd datetime, @ed datetime
set @sd = '2022-01-01' 
set @ed = '2022-01-31' 

select * 
from dbo.Table1 
where date between @sd and @ed

In VBA add:

Public SQLParams As New Dictionary 'Requred Reference "Microsoft Scripting Runtime"

Sub Button1_Click()
  SQLParams("sd") = "'2022-02-01'"
  SQLParams("ed") = "'2022-02-28'"
      
  UpdateQuery SQLParams
End Sub

'Update params in all Query
Sub UpdateQuery(ByRef SQLParams As Dictionary)
  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
      odbcCn.CommandText = SetParamValues(odbcCn.CommandText, SQLParams)
      odbcCn.Refresh
    ElseIf cn.Type = xlConnectionTypeOLEDB Then
      Set oledbCn = cn.OLEDBConnection
      oledbCn.CommandText = SetParamValues(oledbCn.CommandText, SQLParams)
      oledbCn.Refresh
    End If
  Next
End Sub

Function SetParamValues(SQL As String, ByRef Params As Dictionary) As String
  Dim re As New RegExp, Matches 'Requred Reference "Microsoft VBScript Regular Expressions 5.5"
  
  Dim paramName As Variant, paramValue As String
     
  SetParamValues = SQL
  
  re.IgnoreCase = True
  re.MultiLine = True
  
  For Each paramName In Params.Keys()
    re.Pattern = "(set\s+\@" + paramName + "\s*=\s*)(\'[^\']*\')"
    
    paramValue = Params(paramName)
     
    SetParamValues = re.Replace(SetParamValues, "$1" + paramValue)
  Next 'For Each paramName In Params.Keys()
End Function

Answer 2

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
    Next
End Sub

Answer 3

YES - solution is to save workbook in to XML file (eg. 'XML Spreadsheet 2003') and edit this file as text in notepad! use "SEARCH" function of notepad to find query text and change your data to "?".

save and open in excel, try refresh data and excel will be monit about parameters.

Answer 4

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.

Answer 5

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.