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 · 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

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

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

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

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

Examples related to excel

Examples related to vba

Examples related to excel-2007

Examples related to ms-query