In a previous article "Using Excel As a Reporting Engine", we discussed why Excel can be a great tool for rendering complex reports. We then followed up with a run through of the Excel Object Model. In this article we push further by providing a fully functional example. This example consists of an Access database and a module that will be used to render select data in excel. This example was done in Acess 2000 and Excel 2000, but should work in Access 97 and Excel 97.

Example Scenario

You are setting up a financial forecast system for your company. Users enter budget amounts for expenditures and the estimated dates at which these expenditures will be made. The challenge is to create a report that is complete with charts and summary of quarterly expected budget. You decide that excel is the best option for the reporting piece because the report must be viewable by people who do not have MS Access. The report must maintain all formating and more importantly, the report has very complex formatting that is not easily achievable in MS Access.

Tables and Relationships needed

The main requirement of the system is that it keep track of the estimated cashflow of the company at each moment in time so it will be later feasable to look at the company from a quarterly view, monthly view, weekly view or whatever period we wish to report on. With this in mind, we design our system around specific dates rather than months or years, so we can easily aggregate budgets by months, quarters etc..

The design is composed of 4 tables
1) Table 1 - we call CategoryLU and is simply a lookup table that will list budget categories
It contains two fields

CategoryID - autonumber long integer primary key
Category - text field max  50 characters


2) Table 2 - we call simply Budget to list Budget Items and has the following fields
BudgetID - again an autonumber primary key
CategoryID - long integer - the id of category that this budget item belongs to
BudgetName - text field 50 characters

3) Table 3 - we call BudgetAmounts to list amounts budgeted and what dates these were budgeted
BudgetAmountID - autonumber primary key
BudgetID - Long Integer - ties to a Budget
BudgetAmount - currency field
EffectiveStart - the date the budget goes into effect
Amendment - this is to denote budget transfers from one budget to another

4) Table 4 - Payments - record payments against budgets
PaymentID - autonumber primary key
BudgetID - Long Integer - ties to a Budget
PaymentAmt - currency field
PaymentDate - the date the payment was made

The Excel Workbook Generator Module

The workbook generator we created generates an excel workbook containing 3 sheets.

Sheet 1 - Is an excel spreadsheet summarizing budget amounts and payments by category.
Click here to view the code listing

Sheet 2 - Is a chart of payments by Month
(this is generated by the subroutine "ChartBudgetPaymentsCategory" abd is called from the BuildBudgetSchedule subroutine.
Sheet 3- Is list of monthly payments that the chart reads from
Note that in order for this to work, a reference to the excel object model most be specified under "Tools--> References" The excel workbook can be generated by clicking the "Build Excel Spreedsheet" button on the SWITCHBOARD form.

Download Zipped Access 2000 version of Sample Database