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

User · Accepted Answer

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

User · Answer

Excel  39 s interface  160 for SQL Server queries will not let you have a custom parameters   160  A way around this is to create a generic  160 Microsoft Query  then add parameters  then paste your parametorized query in the connection  39 s properties   160  Here are the detailed steps for Excel 2010   Open Excel Goto Data tab From the From Other Sources button choose From Microsoft Query  The  quot Choose Data Source quot  window will appear   160  Choose a datasource and click OK  The Query Qizard Choose Column  window will appear   160  The goal is to create a generic query  I recommend choosing one column from a small table  Filter Data  Just click Next Sort Order  Just click Next Finish  Just click Finish  The  quot Import Data quot  window will appear   Click the Properties    button  Choose the Definition tab In the  quot Command text  quot  section add a  160 WHERE  160 clause  160 that includes Excel parameters   160  It  39 s important to add  160 all the parameters that you  160 want now   160  For example  if I want two parameters I could add this WHERE 1     and 2     Click OK to get back to the  quot Import Data quot  window Choose PivotTable Report Click OK You will be prompted to enter the parameters value for each parameter  Once you have enter the parameters you will be at your pivot table Go batck to the Data tab and click the connections Properties button Click the Definition tab In the  quot Command text  quot  section  Paste in the real SQL Query that you want with the same number of parameters that you defined earlier  Click the Parameters    button  160  enter the Prompt values for each parameter Click OK Click OK to close the properties window Congratulations  you now have parameters

User · Answer

Easy Workaround  no VBA required    Right Click Table  expand  Table  context manu  select  External Data Properties  Click button  Connection Properties   labelled in tooltip only  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

User · Answer

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.

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

The answer is

Examples related to excel

Examples related to vba

Examples related to excel-2007

Examples related to ms-query

Tags