Chapter 24: Excel Query Program

---

Overview

The Excel Query program developed for this book is a practical application of many of the tools and techniques that you learned elsewhere in this book. The program can easily be packaged as an add-in or work as a stand-alone workbook template, plus it combines various forms and toolbar elements into a cohesive program.

Excel Query Program Overview

The Excel Query program is implemented as an add-in to Microsoft Excel. This add-in lets you perform database queries and return the results as part of a worksheet. The interface to the program is through a new command bar, named Excel2k3 VBA Query, and it has four main components: a drop-down list containing the queries that have been executed, a button to edit a query, a button to run the query, and a button to configure the database connection. (See Figure 24-1.)

Figure 24-1: The user interface to the query program is through a new command bar added to Excel.

 CD-ROM  The complete source code for the Excel Query program can be found on the Companion CD in ExcelQuery.xls.

Setting Up the Project

Most of the code for this project is stored in the ThisWorkbook module. Two user forms are also used, DBInfo and DBQuery. The ThisWorkbook module is automatically created when the workbook is created. The user forms can be added to the project by choosing Insert, UserForm from the main menu and modifying the name of the user form in the Properties pane.

At the beginning of each module, you should add the following lines of code. The first statement forces you to declare variables before you use them, which helps to prevent typing errors. The second statement instructs Visual Basic to perform all string comparisons in a case-insensitive manner, which reduces the amount of code you need to write when comparing strings.

Option Explicit
Option Compare Text

To execute any of the database code, the ADO database library needs to be added to the project. Choose Tools, References from the main menu and place a check mark in the box for the Microsoft ActiveX Data Objects 2.7 library. (See Figure 24-2.) Then press OK to close the dialog box and make the library available for use in your application.

Figure 24-2: Use the References dialog box to add a reference to the ADO database library.

Initializing the Program

When the Excel Query program first starts, it uses the Workbook_Open event in the ThisWorkbook 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"
 
Set cb = c.Controls.Add(msoControlButton, 1)
cb.Tag = "Excel2k3 VBA Query Edit"
cb.Style = msoButtonCaption
cb.Caption = "Edit Query"
cb.OnAction = "ThisWorkbook.EditDatabaseQuery"
 
Set cb = c.Controls.Add(msoControlButton, 1)
cb.Tag = "Excel2k3 VBA Query Database"
cb.Style = msoButtonCaption
cb.Caption = "Database"
cb.OnAction = "ThisWorkbook.ShowDatabaseInfo"
 
End Sub 

Normally, if this command bar doesn't exist, the program will generate an error. However, with error checking disabled, the temporary object c will remain, set to Nothing. If c is Nothing, the command bar doesn't exist and will be deleted. This step ensures that the command bar is always created with the proper controls.

Once the program is certain that the command bar doesn't exist, it creates a new floating bar, which can be docked with the other command bars, as shown in Figure 24-1. The remaining statements in the AddCommandBar routine merely add the combo box control, along with the three control button controls used to manage the application.

Two of the actions associated with the command bar controls open user forms. The ShowDatabaseInfo routine shown below opens the DBInfo user form as a modal form.

Private Sub ShowDatabaseInfo()
 
DBInfo.Show vbModal
 
End Sub

The EditDatabaseQuery routine does the same thing with the DBQuery form.

Private Sub EditDatabaseQuery()
 
DBQuery.Show vbModal
 
End Sub

Ending the Program

The Workbook_BeforeClose event calls the DeleteCommandBar routine to ensure that all the changes it made to Excel's menus are removed prior to Excel shutting down.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
DeleteCommandBar
 
End Sub 

The logic in the DeleteCommandBar routine is similar to the logic in the AddCommandBar routine that ensures that the command bar doesn't exist before creating a new one. Error checking is disabled, and a temporary object reference to the CommandBar object is created from the CommandBars collection. If the temporary object reference is valid, the Delete method is used to remove the entire command bar with all its command bar controls from Excel.

Sub DeleteCommandBar()
 
Dim c As CommandBar
 
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
 
End Sub

---