Page 534 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Note One limitation of this approach is that the queries are lost when the user exits
Excel. The program shouldn’t take up a lot of space in the Windows registry just to store the
history of queries, and, while this version of the program doesn’t use an external file that
would need to be loaded and saved each time Excel starts, it would be straightforward to
add a Save/Load set of dialog boxes to this form that would let you save an individual query
to disk.

Executing a Database Query
Clicking the Run Query button on the Excel2k3 VBA Query command bar runs the query
currently selected in the ComboBox on the command bar.

Getting the Information to Run the Query

In the ThisWorkbook module, the RunDatabaseQuery routine, shown here, builds a connec­
tion string using the BuildConnectionString function and gets the query string from the
combo box on the command bar using the GetDBQuery routine. The Trim function is used
to delete any unnecessary blanks as the beginning and the end of the strings.

For more information on using text functions to clean up your data, see Chapter 9, “Manipulating Data
with VBA”.

Sub RunDatabaseQuery()
DimcAsString
DimqAsString
c = Trim(BuildConnectionString)
q = Trim(GetDBQuery)
If Len(c) = 0 Then
DBInfo.Show vbModal

ElseIf Len(q) = 0 Then
DBQuery.Show vbModal

Else
RunQuery c, q
End If

End Sub

Chapter 24

508
Part 6: Excel and the Outside World: Collaborating Made Easy