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?

Cuspidor

You need to be more precise, for instance what kind of SQL are we talking about? (MS SQL Server, Oracle etc.). However, you may in any case read about stored procedures which is the way you pass parameters in SQL unless you prepare a string, that you can update dynamically, and then execute it. – Jarlath

Answer 1

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.

Tasteful

Answer 2

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 & " '" & Range("Cell reference").Value & "'" & 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.

Samy

One more note, the update code won't work right if your parameter has parentheses in it. (it's counting on the parentheses to be able to determine its position in the query string). You could recode the queryPostText string to find its length based on the second (etc) close paren to make it work. – Bis

Hi, I am reading via OLE DB Connection, and my query has total of 3 tables and 2 join, I don't think this can work for me correct? – Bonilla

@Bonilla Yes, it can work, but as I note in the last paragraph, if you are trying to filter (parameterize) based on the same field that is being used for a join, you will have to change the paramPosition setting. – Bis

The Parameters button "ungreys" itself once a table has been formed from a valid external data source... so if you get the table populated (without a parameter), then go back to the connection properties, you should see the Parameters button available for you to set. (Don't forget to use fieldname=? in your SQL query) – Selfdiscipline

@AssadEbrahim That doesn't seem to be the case in the documents I'm talking about. I have valid, working queries in them, and the Parameters button remains disabled. Attempting to add a parameter prompt (?) to the query text produces an error. Is your connection type OLE DB? – Bis

@monocódigo: ODBC. Was responding to "this may work for an ODBC connection..." -- wanted to confirm that it does, but that at first glance even for ODBC the functionality appears grayed out. – Selfdiscipline

@monocódigo: Out of curiousity, is there a reason why you must use (or prefer to use) OLE DB instead of ODBC? – Selfdiscipline

@AssadEbrahim There are a number of reasons why you might want to, but in my case it wasn't a personal choice. I'm working with legacy documents. – Bis

@monocódigo Hi there! I get "object doesn't support this action" when I click the button. Do you have any idea why is that happening? – Forewoman

@GabrielTortelli Hmm. What version of Excel are you running? It could also be that one of your commands contains a typo; might want to double-check the ".CommandText = " line and the ".Refresh" (last) line, specifically. (Any excess periods/dots between the bounds of the "With" statement could also be responsible.) – Bis

Worked. You the best!! – Mechanics

Answer 3

Ok I realise that this question is old, but I had to struggle to find an answer that worked for parameterised SQL query in Excel, and this is the neatest way I could find to do it as of today's date. Hopefully it will help some more people. Firstly, make sure that you have Power Query enabled (https://powerquery.microsoft.com/en-us/excel/). Use "Get Data" and SQL query option, filling out the server name and adding the SQL query code.

With that done, you can create a dummy table(s) in your Excel file that can take parameters provided by the user and feed them into Power Query to filter the number of rows; for example here I used for start and end dates.

The instructions to follow to create the user-defined parameters in Power Query is here ("use a cell value to filter data" option): https://support.microsoft.com/en-us/office/create-a-parameter-query-power-query-5eb365bc-3982-4ab2-8830-b205a69e0f33. Takes a bit of effort but it's much easier than the VBA approach given above.

Liegnitz