Microsoft Office Tutorials and References
In Depth Information
Passing Your Own SQL Statements to External Databases
Figure 18-19: Take note of the connection name (Facility Services, in this example).
3. Close the Workbook Connections dialog box and press Alt+F11 on your keyboard.
The Visual Basic Editor opens.
4. Select Insert➜Module from menu bar.
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. Close the Visual Basic Editor and place a new command button on your worksheet.
To do so, click on the Developer tab, select the Insert drop-down button, and add a Button
Form control.
Refer to Chapter 12 for a detailed overview on using Form controls in your dashboards
and reports.
7. Assign the newly created RefreshQuery macro to the command button, as shown in
Figure 18-20.
Search JabSto ::

Custom Search