07 April 2007

Real World Access (21)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Garry Robinson's Spreadsheets

Excel is a far more popular tool in the real world than Access, and whilst most developers will find it a clumsy tool to develop in, the reality is that many systems are set up in Excel. Irrespective of the obvious technical merits of storing the core data in a database, spreadsheet users have learnt how to achieve good results in a spreadsheet and are generally enthusiastic about their own applications. It is into this world that I find my team's Access skills being called upon time and time again.

So why is an Access developer called into the Excel world in the first place? Usually we are called in to fix up a data management mess or the Excel guru has left the company and the spreadsheet no longer works. For example the spreadsheet user will enter the data for one month and produce a report. Then the next month they copy the spreadsheet and enter the next month’s data and make a small change to the layout of the new spreadsheet. When they are asked by their manager to produce a yearly report, it’s very hard to reference all the spreadsheets because the layouts have changed.

We have a simple solution. We tell the client that we will put all the data in Access and that we will use their very own spreadsheet for reporting. To achieve this we set up an Access reporting form with a date selector, a few filters and a button to export the information into an Excel template *.xlt (that is a slightly modified version of the original spreadsheet). The technology we deploy to manage the transfer of data from Access to Excel and the manipulation of the spreadsheet programmatically is Excel Automation.

Real World examples that we have been involved in this year involve a gold sampling quality control system that had evolved into 50 megabyte spreadsheet, financial data from SAP that was being managed in an array of spreadsheets and a reporting system that checked the costs of drilling invoices.

Tags: ,

Powered by Qumana


Post a Comment

Links to this post:

Create a Link

<< Home