Calling Views and Stored Procedures from Excel - Microsoft Business Intelligence Tools for Excel Analysts - page 218

Figure 11-7: Running an SQL Server stored procedure with parameters.
Using VBA to create dynamic connections
You may have noticed that the last few examples have hard-coded the criteria in the SQL statements.
For example, in Figure 11-7, Tulsa is specified directly into the SQL statement Where clause. This
obviously would cause the data being returned to always be data for Tulsa.
But what if you want to select a market and have the SQL statement dynamically change to respond
to your selection? Well, you can use a bit of Excel VBA (Visual Basic for Applications) to change the
SQL statement on-the-fly. Follow these steps:
1. Designate a cell in your worksheet that will catch the dynamic selection for your criteria.
For example, in Figure 11-8, cell C2 is where users can select a market. You typically give users
a way to select criteria with either a Combo Box or a Data Validation list.
Figure 11-8: Designate a cell that will trap the criteria selection.
2. Click the Connections button on the Data tab.
Take note of the name for the connection you want to dynamically change.
3. Close the Workbook Connections dialog box and press Alt+F11.
The Visual Basic Editor opens.
4. Choose Insert ➜ Module.