MS-Excel Sample Applications

The following sample applications are included when you purchase the Visual Basic 6 Tutorial Download Package or the Visual Basic 6 "Extra" Sample Applications from The VB Programmer.

1. Generate an MS-Excel Spreadsheet "from Scratch"

This sample application demonstrates how to generate an MS-Excel spreadsheet "from scratch", meaning that the program alone creates the Excel spreadsheet without using a template as a basis.

This sample application uses the Northwind database and generates a sales report for a user-specified date range. The interface for the program, shown below, is rather plain – it just lets the user select the desired date range, along with options for saving and controlling the display of Excel:

Assuming the user has selected the "Saving Option" to save to a filename and location of his or her choice, a dialog box appears:

A screen shot of the resulting Excel report is shown below. Note that all formatting (fonts, colors, etc.) were controlled by the VB program. This demo also shows how to use and control the Data/Subtotal feature of MS-Excel.

2. Generating a Template-Based Report Using MS-Excel

This sample application demonstrates how to generate a template-based report with Excel. Note: When we say "template",  we are NOT referring to an Excel "template" in the strictest sense (i.e., an MS-Excel ".xlt" file) – what we mean by "template" is simply another Excel file (".xls" file) that contains the necessary formatting that can be used as a basis for what we want the application to generate. This technique is particularly useful for reports where the format will remain static from run to run.

In the case of this application, the "template" is simply an Excel .xls file that a row for each product sold by Northwind Traders, with columns for one week's worth of data. The idea is that we want to show the total number (quantity) of each product sold for a given week (if none of the product was sold, the corresponding cells will simply remain blank). A screen shot of the template file is shown below:

The interface for the program, shown below, is very similar to that used in the first demo application – it lets the user select the desired "week beinning" date, along with options for saving and controlling the display of Excel:

A screen shot of the Excel output is shown below. Note that the dates were filled, based on the starting date provided by the user. Also, the quanities for each product sold during the week are populated in the appropriate cells, and are summed in the Weekly Total column.