Excel Datasheet Generator
Background
An existing customer has a Global installation of PPDS for Windows. Each corporate workstation has the full client server installation with each local thermoserver pointing to shared network locations for the databanks.
In order to simplfy the generation of standard tables of data they requested a simple tool that would allow them to:
- consolidate a number of existing tools into one new tool;
- minimise installation issues, particlarly with regard to updating the tool;
- generate reports in the format that they were familiar with;
- generate reports that they could manipulate if required;
- have reports that were 'static' and would not update if they were re-opened sometime after generation.
After discussing options with the client the decision was made to proceed with developing a VBA based application within MS Excel.
This had the benefits of allowing full access to the PPDS API while allowing the resultant sheets to be simply written rather than having functions embedded in them.
As each workstation already had a copy of MS Excel the installation would consist of deploying the resultant Excel template into their corporate templates area.
With this step agreed, the client defined the specifc calculations they required and supplied sample output and development of the "Excel Template" began.
Development
A simple interface was developed - a single button on a single sheet workbook.
Screenshot of the Excel Datasheet Generator
The user pressed this button to initialise a connection with the Thermoserver and display the main dialog.
Screenshot of the main user dialog from teh Excel Datasheet Generator
When the dialog initially displays, the previous calculation options are restored, having been stored in the registry. The user can add or remove calculation options and define to conditions, units and reporting options.
The required component is selected from the available banks via its own dedicated screen:
Screenshot of the Component Search utility within the Excel Datasheet Generator
The banks can be searched for exact matches or for matches that either start or contain the entered string. The list of matches can then be examined to help select the correct component.
Once the conditions have been adequetely defined, the "Compute" button becomes active and allows the suer to press it to start the calculation.
New sheets are added to the workbook as their dedicated calculation is performed with each sheet being generated according to strict formatting detail described by the client.
Typical output from the Excel Datasheet Generator
Once the calculations are complete the resultant workbook can be saved and treated like any other workbook.
The naming algorithms for the sheets allows multiple calculations to be performed with the same component without overwriting existing data
Where is it now?
The Excel template is still regularly used by the company as the main property tool for the majority of staff. New functionality is regularly added as part of the client's maintenance plan.
Linked Content:
