Page 525 - Microsoft Office Excel 2003 Programming Inside Out

Excel Query Program

Initializing the Program
When the Excel Query program first starts, it uses the Workbook_Open event in the This-
Workbook module to call the AddCommandBar routine that adds the Excel2k3 VBA Query
command bar to Excel.

Private Sub Workbook_Open()

AddCommandBar
End Sub

The AddCommandBar routine begins by disabling error checking with the On Error Resume
Next statement. Then the routine tries to create an object reference to the Excel 2k3 VBA
Query command bar by referencing its name in the CommandBars collection.
Private Sub AddCommandBar()

Dim c As CommandBar
Dim cc As CommandBarComboBox
Dim cb As CommandBarButton
On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
If Not c Is Nothing Then
Application.CommandBars("Excel2k3 VBA Query").Delete
End If

Set c = Application.CommandBars.Add("Excel2k3 VBA Query", _
msoBarFloating, False, True)
c.Enabled = True
c.Visible = True

Set cc = c.Controls.Add(msoControlComboBox, 1)
cc.Tag = "Excel2k3 VBA Query Statement"
cc.Text = "<enter a query>"
cc.Width = 200
cc.OnAction = "ThisWorkbook.EnterDatabaseQuery"
Set cb = c.Controls.Add(msoControlButton, 1)
cb.Tag = "Excel2k3 VBA Query Run"
cb.Style = msoButtonCaption
cb.Caption = "Run Query"
cb.OnAction = "ThisWorkbook.RunDatabaseQuery"

Chapter 24

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