The 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. Two Report automation examples, are demonstrated on this page. The first reporting example shows how our Excel consultants extract the raw data on a worksheet and populated 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.
Database Programming and Reporting Automation
Database Programming Example Details
In this example, 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 (1) Details
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 uses 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
Excel Reporting Automation Example (2) Details
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
Figure 7. Screen shot of the Update Parameters interface
Figure 8. Screen shot of Print Optin interface
Figure 9. Screen shot of the different reports generated
37 advanced Excel VBA prorgrams in finance & statistics with VBA source codes.
