Microsoft Office Tutorials and References
In Depth Information
Passing Your Own SQL Statements to External Databases
Some stored procedures require parameters (criteria inputs) to run successfully. If your stored
procedures require parameters to run, you can simply include them in your SQL statement.
Figure 18-17 illustrates this by running a stored procedure with two parameters:
➤ A parameter that passes the required market name
➤ A parameter that passes the required quarter
Figure 18-17: Running an SQL Server stored procedure with parameters.
Using VBA to create dynamic connections
You may have noticed that the preceding examples hard-coded the criteria in the SQL statements.
For example, in Figure 18-17, Tulsa is specified directly in 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 VBA to change the SQL statement on the fly. Follow these
1. Designate a cell in your worksheet that will catch the dynamic selection for your criteria.
For example, in Figure 18-18, 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 18-18: Designate a cell to trap the criteria selection.
2. Click the Connections button on the Data tab to open the Workbook Connections
Note the name for the connection you want to dynamically change. In Figure 18-19, the
connection name is Facility Services.