|Back to Papers and Articles|
Rendering the results of an Access Database in Excel
Copyright 2001 Paragon Corporation ( August 11, 2001)
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 ScenarioYou 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 neededThe 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 ModuleThe 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
Back to Papers and Articles