Querying & Reporting: Using Visual Basic to Drive Excel 97

Combine two applications into one powerful tool

Here is a simple application that leverages Visual Basic (VB) and Excel by integrating them into a powerful developer tool. Because you can use Microsoft Office with other applications, you can create applications quickly in Office's features with VB and SQL Server. For example, if you need to add worksheet calculation features to your application, why write them yourself when you can use Excel's calculation features? You can download the code to create the sample application at http://www.sqlmag.com at the link to this article. The code works with VB 5.0 or 6.0, Excel 97 or 2000, and SQL Server 6.5 or 7.0. The Microsoft Office 97 Visual Basic Programmer's Guide (Microsoft Press) might help you with this project. (The Readme.txt file in the downloadable code includes the steps to create the sample database.)

This combined VB and Excel application uses Excel to host an expense report and leverage Excel features that let users easily enter and review expense-report data in a familiar way. The ExpenseTracking.xls workbook this application uses lets a user enter expenses each month, and Excel stores them as separate worksheets in the workbook. Then the summary page in the ExpenseTracking.xls workbook lets the user view an expense summary.

The Visual Basic for Applications (VBA) code in Listing 1 handles the What If analysis of the Excel application. The accompanying VB application in Listing 2, page 56, can read data in the worksheets and display the data in a VB application's user interface (UI). A combined application also can store the data in the SQL Server Expenses table. In a reverse querying and reporting application, first you create the worksheet or report, then the application puts the data in the database. Of course, you can also drive Excel in the opposite direction by taking data from SQL Server and placing it into an Excel worksheet. Then create a new database called MyCompany and run the SQL Server code to create the table. The Excel workbook in the following example has three worksheets, Summary, Sep, and Oct, which contain several named cells and ranges. The cell and range names make dealing with the cells programmatically easier and more logical.

Let's look at how the combined application works. VB uses Excel's Automation interface to control the application. The Automation interface exposes most of Excel as a set of objects that let developers use almost any language access the object's features. You can use the Automation interface to control Excel as you would if you were programming Excel using VBA internally.

The first step in controlling Excel from VB is to create a reference to the Excel type library. Open the References dialog from the Project menu, then select the Microsoft Excel 8.0 Object Library. This selection connects the VB application to Excel, which lets you use Excel's many features, such as its worksheet or graphing capabilities.

Screen 1, page 57, shows the GUI, which changes as the user interacts with the application. When the application starts, only one button appears on the form. Clicking that button populates and displays the list in the combo box. Selecting an entry from the list in the combo box displays other elements. As the example in Screen 1, on page 57 shows, when the user selects Summary, the month and total expenses appear.

Next, you need to create variables to use with the Excel objects. Callout A in Listing 2 creates the xlApp, xlWrk, and xlSht variables. The application uses these primary variables (and several other variables in Callout A) to interface with Excel. The arrays ReportMonths and ReportAmounts will contain the summary data; they are read when the user saves the data. The code sets the xlsPath variable to the worksheet's path name.

The code in Callout B executes when the user clicks the Get Worksheet Names button. The code starts Excel by creating an instance of the Excel Application object. Next, the code opens the workbook, using the xlsPath variable to supply the path to the workbook.

Next, in Callout B, a For Each loop goes through the worksheets in the workbook, adding each worksheet name to the lstWorkSheets list. So far, the code starts Excel, opens a workbook, examines the worksheets, etc., as if you were running Excel.

If users need to shut down Excel from VB, they can click the Close Excel button, which Screen 1 shows. Clicking this button executes the code in Callout C. The first line of code calls the ShutDownExcel procedure. ShutDownExcel closes the current workbook, then destroys the Excel object references created earlier. The Save Worksheet Information button saves the data in the worksheet to the SQL Server Expenses table in the MyCompany database.

Callout D creates a reference to the worksheet that a user selects from the list in the combo box in Screen 1. Then the code checks this worksheet name with a Select Case statement, which loads either the summary or the month data by calling the appropriate procedure (LoadSummary or LoadMonth).

When the user selects Summary data from the list, Callout E executes. The first line of code after Callout E uses the Range object's Count property to extract the number of items in the Expense column. The code passes the Expense column name as an argument into the LoadSummary procedure.

The next line of code starts a With block that references the Summary sheet. Selecting Summary from the list sets the xlSht variable to reference the Summary sheet. The next few lines of code extract values from the worksheet by using either the Cells collection or the Range object, depending on the cell or the range that the command operates on and the task that the command performs. As the code processes each row in the worksheet, it places the values for the month and the expense amount in the ReportMonth and ReportAmount arrays, respectively.

The code in Callout F also loops through the rows in a worksheet and extracts certain values. The code is similar to that in LoadSummary, but uses only the Cells collection.

The database code is in Callout G and Callout H. The code in Callout G creates the variables this procedure uses. Callout H creates the SQL statement using a For Next loop. The code in Callout H loops through the two arrays (ReportMonth and ReportAmount) and completes the SQL statement by adding the values from the arrays to the statement. Next, the RunWithRS_RW method executes the SQL statement. RunWithRS_RW is in Module1.bas. (You can download Module1.bas at http://www.sqlmag.com at the link to this article.) Module1.bas contains all the ADO functions that I use in this application.

At this point, the application code is complete. From now on, when you execute the VB application and interact with it, the VB application launches Excel. Excel doesn't display a UI, but merely runs in the background and does the work for the VB application. Note that the application won't work correctly if Excel is running when you start it.

You can see that Excel and VB make a powerful team. When using these tools together, you can access data in SQL Server and use the data for reporting or updating. The power of these tools comes from their integration and ability to share the VB programming language. It's also worth noting that generally you can take VB code and move it from one platform to another. For example, you can place the ADO procedures into the Excel VBA module and execute the code from there.