Querying External Data in Excel

What is External Data
Create a query in the UI
The External Data Toolbar
Edit a query in the UI
Create a query with VBA
Delete a query with VBA
Change the SQL with VBA
Change the database location


Now the fun part.  Instead of entering  a value in your Where clause on which to filter, you can make MSQuery prompt the user for a string.  These special criteria are called Parameters.

Entering Parameters in the User Interface

Parameters can only be entered in the user interface via MSQuery.  There is not a provision in the Wizard to enter a parameter.  The last dialog of the Wizard allows you to view the query in MSQuery.  Once in MSQuery, show the Criteria if they are not already visible.  Under the view menu, choose Criteria:

Assume you've already filtered you data with a criterion of City='Berlin'.  Your criteria pane will look like this:

To change this criterion to a parameter, that is, to have MSQuery prompt you for a City value on which to filter, enter a prompt string enclosed in brackets [].

When you return to Excel, you will be prompted to enter a City value with this dialog:

Enter "London" without the quotes and click OK.  Now your worksheet will look like this (if it's set up like the other examples on these pages)

Each time you refresh this query, you will be asked to "Please Enter City" and can change your filter on the fly.

The Parameters Toolbar Button

On the External Data Toolbar, there is a button for Parameters.  It is disabled if there are no parameters in your query.  Unfortunately, there is no way to add a parameter in Excel's user interface.  As far as I know, you must use MSQuery to do it.  The Parameters Toolbar Button shows the Parameters dialog:

The listbox on the left shows all the parameters associated with this query, one in this case.  There are three types of parameters, namely Prompt, Constant, and Range.  A Prompt parameter is what we just set up.  It prompts the user to enter a value using a customizable string.  The Constant parameter uses the same value each time you refresh.  From a practical perspective, it's not much different than having a standard criterion instead of a parameter.  The Range parameter allows you to specify a cell on your worksheet that will contain the value used in the criterion.  As far as I know, you can't specify a named range in this box, it must be A1 style cell reference.  When you select a Range parameter, the "Refresh automatically ..." checkbox becomes enabled and you can select it if you wish.

Parameters in VBA

When a parameter is created, the SQL (CommandText property) of the QueryTable is changed.  A question mark is used by the compiler to hold the place of the parameter.  The SQL for the above query now looks like this:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.City
FROM `C:\NewFolder\Northwind`.Customers Customers
WHERE (Customers.City=?)

The other options offered by the Parameters dialog are stored as properties of the Parameter object.  You can create a parameter in VBA merely by changing the SQL.  This will also create a Parameter object.  As an example, assume again that you have an SQL like this:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.City
FROM `C:\NewFolder\Northwind`.Customers Customers
WHERE (Customers.City='Berlin')

To change this to a parameter query via VBA, manipulate the string that is the CommandText property to include a question mark instead of 'Berlin'

Sheet3.QueryTables(1).CommandText = _   

Creating a parameter in this way creates a Parameter object using the default properties.  That is, it creates a Prompt parameter with the name Parameter1 and a prompt string of "Parameter1".  The good news is that now that Parameter object has been created, the Parameters toolbar button is enabled and you can gussy up your parameter in there.

To accomplish the whole task in VBA, you can explicitly change the properties of the Parameter object.  Here's an example sub that adds a Range parameter that looks to cell J1 for its value:

Sub CreateParam()

     Dim oQuery As QueryTable
     Dim oParam As Parameter

     Set oQuery = Sheet3.QueryTables(1)

     oQuery.CommandText = Replace(oQuery.CommandText, "='Berlin'", "=?")

     Set oParam = oQuery.Parameters.Add("CityParam")

     oParam.SetParam xlRange, Sheet3.Range("J1")
     oParam.RefreshOnChange = True


End Sub

Deleting a Parameter in VBA

The way that VBA handles parameters is nothing short of a mystery.  Parameter objects are created for each parameter that exists in the SQL when you refresh a table.  However, parameters are NOT automatically deleted just by refreshing.  If you rerun the Wizard or edit the query in MSQuery, the unused parameters are deleted.

Furthermore, VBA does not provide a Delete method to the Parameter object.  If you want to clean out the parameter closet, you have to use the Delete method of the Parameters Collection object.  That's right, you have to delete ALL the parameters.

So, how do you do it then.  You would have to count the number of question marks in the WHERE clause, loop through the existing Parameter objects and save all the possible properties to variables.  Then delete all the parameters and recreate those you need using the properties stored in the variables.  Hey, if you find an easy way to do all that, be sure to drop me a line.




                    Contact Me            Visit Daily Dose of Excel - an Excel weblog