how to pass parameters to query in SQL Excel

Question

I  linked  Excel to Sql and it worked fine - I wrote some SQL script and it worked great  All I want to do is to pass parameter to query  Like every time I make refresh I want to be able to pass parameter  filter condition  to Sql Query   In  Connection Properties  Parameters button is disabled  So I can t make parameter query   Can Anyone help me

User · Accepted Answer

It depends on the database to which you re trying to connect  the method by which you created the connection  and the version of Excel that you re using   Also  most probably  the version of the relevant ODBC driver on your computer    The following examples are using SQL Server 2008 and Excel 2007  both on my local machine   When I used the Data Connection Wizard  on the Data tab of the ribbon  in the Get External Data section  under From Other Sources   I saw the same thing that you did  the Parameters button was disabled  and adding a parameter to the query  something like select field from table where field2      caused Excel to complain that the value for the parameter had not been specified  and the changes were not saved   When I used Microsoft Query  same place as the Data Connection Wizard   I was able to create parameters  specify a display name for them  and enter values each time the query was run  Bringing up the Connection Properties for that connection  the Parameters    button is enabled  and the parameters can be modified and used as I think you want   I was also able to do this with an Access database  It seems reasonable that Microsoft Query could be used to create parameterized queries hitting other types of databases  but I can t easily test that right now

User · Answer

This post is old enough that this answer will probably be little use to the OP  but I spent forever trying to answer this same question  so I thought I would update it with my findings   This answer assumes that you already have a working SQL query in place in your Excel document   There are plenty of tutorials to show you how to accomplish this on the web  and plenty that explain how to add a parameterized query to one  except that none seem to work for an existing  OLE DB query   So  if you  like me  got handed a legacy Excel document with a working query  but the user wants to be able to filter the results based on one of the database fields  and if you  like me  are neither an Excel nor a SQL guru  this might be able to help you out   Most web responses to this question seem to say that you should add a         in your query to get Excel to prompt you for a custom parameter  or place the prompt or the cell reference in  brackets  where the parameter should be   This may work for an ODBC query  but it does not seem to work for an OLE DB  returning    No value given for one or more required parameters    in the former instance  and    Invalid column name    xxxx       or    Unknown object    xxxx       in the latter two   Similarly  using the mythical    Parameters       or    Edit Query       buttons is also not an option as they seem to be permanently greyed out in this instance    For reference  I am using Excel 2010  but with an Excel 97-2003 Workbook    xls    What we can do  however  is add a parameter cell and a button with a simple routine to programmatically update our query text   First  add a row above your external data table  or wherever  where you can put a parameter prompt next to an empty cell and a button  Developer- Insert- Button  Form Control      You may need to enable the Developer tab  but you can find out how to do that elsewhere   like so     Next  select a cell in the External Data  blue  area  then open Data- Refresh All  dropdown - Connection Properties    to look at your query   The code in the next section assumes that you already have a parameter in your query  Connection Properties- Definition- Command Text  in the form    WHERE         DB TABLE NAME Field Name       Default Query Parameter       including the parentheses    Clearly    DB TABLE NAME Field Name    and    Default Query Parameter    will need to be different in your code  based on the database table name  database value field  column  name  and some default value to search for when the document is opened  if you have auto-refresh set    Make note of the    DB TABLE NAME Field Name    value as you will need it in the next section  along with the    Connection name    of your query  which can be found at the top of the dialog   Close the Connection Properties  and hit Alt F11 to open the VBA editor   If you are not on it already  right click on the name of the sheet containing your button in the    Project    window  and select    View Code      Paste the following code into the code window  copying is recommended  as the single double quotes are dicey and necessary    Sub RefreshQuery    Dim queryPreText As String  Dim queryPostText As String  Dim valueToFilter As String  Dim paramPosition As Integer  valueToFilter    DB TABLE NAME Field Name      With ActiveWorkbook Connections  Connection name   OLEDBConnection      queryPreText    CommandText      paramPosition   InStr queryPreText  valueToFilter    Len valueToFilter  - 1      queryPreText   Left queryPreText  paramPosition       queryPostText    CommandText      queryPostText   Right queryPostText  Len queryPostText  - paramPosition       queryPostText   Right queryPostText  Len queryPostText  - InStr queryPostText         1        CommandText   queryPreText  amp        amp  Range  Cell reference   Value  amp       amp  queryPostText  End With  ActiveWorkbook Connections  Connection name   Refresh End Sub   Replace    DB TABLE NAME Field Name    and  Connection name   in two locations  with your values  the double quotes and the space and equals sign need to be included    Replace  Cell reference  with the cell where your parameter will go  the empty cell from the beginning  - mine was the second cell in the first row  so I put    B1     again  the double quotes are necessary    Save and close the VBA editor     Enter your parameter in the appropriate cell    Right click your button to assign the RefreshQuery sub as the macro  then click your button   The query should update and display the right data   Notes  Using the entire filter parameter name   DB TABLE NAME Field Name     is only necessary if you have joins or other occurrences of equals signs in your query  otherwise just an equals sign would be sufficient  and the Len   calculation would be superfluous  If your parameter is contained in a field that is also being used to join tables  you will need to change the  paramPosition   InStr queryPreText  valueToFilter    Len valueToFilter  - 1  line in the code to  paramPosition   InStr Right  CommandText  Len  CommandText  - InStrRev  CommandText   WHERE     valueToFilter    Len valueToFilter  - 1   InStr  CommandText   WHERE    so that it only looks for the valueToFilter after the  WHERE    This answer was created with the aid of datapig   s    BaconBits    where I found the base code for the query update

[sql] how to pass parameters to query in SQL (Excel)

Examples related to sql

Examples related to excel

Examples related to parameter-passing