Page 533 - Microsoft Office Excel 2003 Programming Inside Out

Excel Query Program

On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
cc.Text = Query.Text

End If
End Sub
Notice that saving the query in this fashion triggers the OnAction event associated with this
control, which is the EnterDatabaseQuery routine located in the ThisWorkbook module,
which is shown here. This routine scans the list of queries stored in the control and appends
the new query to the end if it isn’t found, letting users rerun a particular query quickly.

Sub EnterDatabaseQuery()
Dim c As CommandBar
Dim cc As CommandBarComboBox
DimqAsString
DimiAsLong
On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
q = cc.Text
i=1
Do While i <= cc.ListCount
If q = cc.List(i) Then
Exit Sub

End If

i=i+1
Loop
cc.AddItem cc.Text

End If
This routine begins by locating the CommandBarComboBox control, and then saves the value
from the Text property into a temporary variable named q. Next the routine uses a Do…While
loop to scan through the list of queries stored in the drop-down list. If a match is found, the
Exit Sub statement ends the routine without action. If no match is found, the AddItem
Chapter 24
method is used to add the query to the end of the list of drop-down items.

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