|
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 = _
Replace(Sheet3.QueryTables(1).CommandText,"='Berlin'","=?")
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
oQuery.Refresh
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.
|
