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

5. Enter the following code in the newly created module:

Sub RefreshQuery()

ActiveWorkbook.Connections("Facility Services").OLEDBConnection.
CommandText = _
"SELECT * FROM [Sales_By_Employee] WHERE [Market] = '" & _
Range("C2").Value & "'"

ActiveWorkbook.Connections("Facility Services").Refresh
End Sub

This code creates a new macro called RefreshQuery. The RefreshQuery macro points to the
correct connection (Facility Services) and specifies the command text for that connection.

The command text is essentially the SQL statement you want the connection to run when
triggered. In this example, the command text selects from the [Sales_By_Employee]
table and sets the criteria for the [Market] field to the value in cell C2. The code then
refreshes the Facility Services connection.

6. (Optional) Place a button on your worksheet to run your macro.

Click the Developer tab in the Excel Ribbon and select Button Form control from the Insert
drop-down list.

7. Select your macro and click OK.

The Developer tab is hidden by default in Excel. To enable the Developer tab, choose
File ➜ Options ➜ Customize Ribbon. Select the Developer Tab check box, and click OK.
The Developer tab now appears on the Ribbon.

Tip

You now have a button that allows for the dynamic extraction of data from your external database
based on the criteria you specified (see Figure 11-9).

Figure 11-9: You now have an easy-to-use mechanism to pull external data for a specified market.