Database Programming Example

This database programming example demostrates how our Excel consultants using Excel programming to extract data from a MS-Access database and present them in an Excel spreadsheet. The source data which stored in the tables (see Figure 1) of a Microsoft Access relational database is retrieved and populated into a report in an Excel worksheet (see Figure 2) through OLEDB (Object Linking and Embedding Database) and ADO (ActiveX Data Objects).

Figure 1. Screen shot of a table relationships chart

Figure 2. Screen shot of the report generated from the database

Excel Reporting Automation Example - Parameter Driven Report (1)

This project extracts data from an Excel worksheet (see Figure 3) and populates the information based on the criteria filter (see Figure 4) into a report (see Figure 5). The reporting process is slightly more complex due to the multiple demension of the data (region, country, service, quarter, and year). In additional, the report does not use a pre-styled template since the number of rows or column on the report is unknown before the users make their selection using the filter. Array technique is used in this project to accommandate for the multi-demension of the date and the report output variation.

Figure 3. Screen shot of raw date in a worksheet

Figure 4. Screen shot of the record filter user interface

Figure 5. Screen shot of a final report generated from the raw data

Figure 7. Screen shot of the Update Parameters interface

Figure 8. Screen shot of Print Optin interface

Three Report automation examples, are demonstrated. The first reporting example shows how our Excel consultants extract the raw data on a worksheet and populate the information into a report. The second reporting example shows how to incorporate parameters in the report automation process and place the calculated values into the report. Then a report is generated for each of the selected records. The third one is a report consolidation example.

Excel Reporting Automation Example - Parameter Driven Report (2)

This project uses different Excel files as the data source. One of the major features of this program is that, it allows users to select the company file (see Figure 6), define the computational parameters (see Figure 7), select the names of the records (see Figure 8), and build one separate report for each of the records selected (see Figure 9).

Figure 6. Screen shot of the Select Company interface

Projects Sample Directory Map

Quick Contact

Figure 10. Report consolidation screen shot.

This section demonstrates on how our Excel consultants use MS-Access to develop data entry forms for a database with many-to-many relationship tables.

Data Entry Form Example

This MS-Access application allows users to input data into multiple database tables that have many-to-many relationship (see Figure 1). By using multiple subforms, users can update data in multiple tables on one master data entry form (see Figure 2).

Figure 1. Screen shot of many-to-many relationship tables in the database

Figure 2. Screen shot of the master data entry form

Excel Reporting Automation Example - Report Consolidation

This example demonstrates how individual business reports can be summarized into a consolidated master report despite that each of the business reports has its own unique format and styles. Through Excel report automation, all the business reports are coming together in one with and with less chance to make human errors.

Copyright © Excel Business Solutions. All Rights Reserved.