I often use MS Query to pull external data into Excel. I find it to be an extremely powerful way to quickly pull together interactive reporting without a lot of effort. If you're new to MS Query, you may want to check out Kusleika's intro to MS Query to get the gist.
.
Anyway, one of the more common requests I get is to allow a user to dynamically pass multiple criteria to a parameter. For example, a manager may want to pull in data for Tulsa, and Dallas, and Seattle at the same time.
.
In this example, I've built a model where a user can use a Multi-select Listbox to choose Markets. The MS Query responds accordingly to bring back the chosen Markets.
.
The selection of different Markets brings back another set of data.
.
What's interesting about this is that I only have one parameter built into my MS Query connection. That's right; I'm passing multiple markets to one parameter.
Here's how it's done:
.
Step 1: Build a basic MS Query connection without any parameters
Here, I've built a query that gives me all Markets.
.
Step 2: Right-click anywhere inside the query table and select External Data Properties
.
Step 3: Click Connection Properties
.
Step 4: Add a WHERE clause that passes a parameter through an Instr function
The Instr function is designed to find the occurrence of a string within another string, returning the position number for the string you are trying to find.
The basic syntax is Instr(String to Search, String to Find)
In this example, I am using Instr(?, [Market]). This will evaluate each market to see if it is found in the parameter I am passing.
So if I literally pass multiple markets to my parameter separated by commas, (for example; "Denver, Tulsa, California"), all matching markets will evaluate to a position number greater than zero.
.
The long and short of it is that a WHERE clause is added to the query that will only return records where the Market field evaluates to a position number greater than zero when thrown against my parameter. Thus:
WHERE (Instr(?, [Market])>0)
.
Step 5: Set up your parameter to use a cell reference for future refreshes
In this example, I'm telling my query to get the parameter values from cell A1
.
Once that is set up, any value placed in Cell A1 will trigger the MS Query to refresh.
.
And because of our cool Instr function, we can request multiple markets in that one parameter.
.
Setting up the Multi-Select ListBox
If you want to get fancy, you can add an ActiveX Listbox and configure it to be multi-select.
.
Once you have your Listbox set up, you'll have to use a bit of code to pass the selected items to cell A1 ( the trigger for the query table).
Dim lItem As Long
Dim strSelected As String
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) = True Then
strSelected = strSelected & ListBox1.List(lItem) & ", "
End If
Next lItem
'Update the trigger cell
Range("A1").Value = strSelected
'Refresh the query table
ActiveSheet.QueryTables("Query from MS Access Database").Refresh False
End Sub
.
Excel 2003
You’ll notice that I’ve conspicuously not mentioned Excel 2003 yet. Well this technique gets complicated in Excel 2003 - for a few reasons.
First, there is no easy way to change the SQL statement after the MS Query is built in Excel 2003. You can do it via VBA, but it’s not practical enough to try to describe it here.
Second, MS Query has a conniption when you try to add the fancy Instr parameter in the MS Query Interface. I’ve only been able to apply this technique by changing the SQL outside of MS Query.
Hopefully someone will suggest some obvious solution for Excel 2003 that I haven’t been able to find.
.
*****UPDATE*****
Dick Kusleika and Jan Karl Pieterse developed an add-in that enables the editing of SQL statements in Excel 2003! You can get it at http://www.jkp-ads.com/download.asp. The add-in is called QueryManager. With this add-in, you can employ this technique without fighting MSQuery.
This is very cool, Mike. From memory, I think there's some problem with using parameters with Pivot Tables...are your aware of anything to this end? (I can't check this as I'm on holiday. As you can see, my idea of a holiday includes reading your blog. Consider yourself flatterred).
I use the QueryManager add-in that JKP and I developed to change the SQL statement in 2003. It's not perfect, but it's a heck of a lot better than Editing in MSQuery for simple changes. You can get it at http://www.jkp-ads.com/download.asp
Thanks DK. I've added an update to the post.
The Query will not refresh when you deselect items from the list box.
You need to add the following before End Sub
Range("A1").Value = strSelected
'/----ADD CODE---------
Range("A1").Select
Sheet1.QueryTables("myQuery").Refresh BackgroundQuery:=False
End Sub
Sam: Agreed. I've updated the code.
Thank for the heads up.
Mike, shouldn't you qualify what databases this technique works with? It doesn't work with T-SQL, it doesn't work with Pervasive, it doesn't work with Progress (OpenEdge)... In fact, the only databases that appear to work are those based on Jet. For this kind of work, I end up constructing an SQL string in VBA with the selected list in an "In" function (e.g. WHERE [Market] In()). The string is then used to set the CommandText property of the QueryTable object. The disadvantage of this approach is that it requires a bit of VBA, but you go there anyway if you use a listbox. The advantages are that it works with any database and it allows you to "parametize" PivotTables by using the SQL string to set the CommandText property of the PivotCache object.
This is great, but I can't get it to work with an ODBC link to an ORACLE database...
Either it crashes Excel 2007, or I get an Incorrect parameter type...message from excel.
Colin and Thierry: Unfortunately I'm not able to test this on anything other than SQL server and Access.
I do know that Oracle supports the Instr function so I'm not sure why this would not work. I would re-look the SQL syntax and possibly add the optional Instr arguments.
Mike, how did you get this to work with SQL Server? SQL Server doesn't support Instr, and attempting to pass a parameter into the equivalent CHARINDEX function, like CHARINDEX(?, )>0, doesn't work.
That should read CHARINDEX(?, fieldname)>0.
Colin: I didn't get it to work in SQL Server. I've never tried to use this technique with SQL server until now.
At first glance it seems as though data type issues are the culprit in preventing the us from hitting SQL Server.
I know that CharIndex will accept char, nchar, varchar, nvarchar, text, and ntext strings, but for some reason, it won't recognize the values fed from a parameter.
So then I tried passing the parameter through the Convert and Cast functions but apparently the SQL Server Driver complained that the parameter value can't be derived when used as an argument in a function.
So the real issue seems to be that the SQL driver (and presumably the Oracle driver) can't translate or interpret a parameter value when fed as an argument in a function. That is what I'm concluding anyway.
Nevertheless, Access doesn't seem to have this problem.
And Colin has been right all along. This technique would seem to be severely limited to Access databases.
Mike, I went through the same sequence (including the Cast and Convert bit). CharIndex is totally anal. Because it returns an integer, it seems to expect an integer parameter. Feed it an integer parameter, and Excel complains that it's not valid for the argument (of course). Feed it text, and Excel complains that it's a bad parameter type. Grrr!