Template Workbooks
Once an Excel workbook has become useful, it is likely to be copied. Maybe a Sales ledger workbook has proved useful in one office and there is then a temptation to copy the workbook to other offices so they can use the functionality. Unfortunately this means that any problems or improvements to the original workbook must be replicated across multiple copies. Any amendments to the way one sheet operates (Transform*) needs to be manually applied to every other copy.
The real problem is that the spreadsheet holds both the data and the functionality to transform it into something useful. We have seen that the PersisTables toolkit allows data to be secured and shared. So we can use these tools to extract the data from the spreadsheet so all that remains is the functionality.
This approach means that a single template workbook can be used on different data sets. By loading the data set appropriate for the user into the template workbook, it allows each user to access the data they want using common functionality. If a change is required it is applied to the template workbook (Transform=>Transform*) and the same data can be applied to the new Template workbook to give modified results.
Making the template workbook ‘read only’ means it cannot be altered by one user and saved, having an adverse impact on others. No longer will an overwritten formula accidentally saved cause a problem when the workbook is reopened.
A more detailed description of how a spreadsheet can be built which captures user input, retrieves historic inputs and allows the data to be saved is provided in the PersisTables User Guide available with the installed package.
PersisTables enables the transformation of existing EUC applications into an entirely new architecture which separates data from function. By utilising PersisTables to capture the input and output data, the modified workbook can be transformed into one where data is managed externally from Excel and only loaded as required.
This new architecture strictly separates input and output data from any transformation process. As no data resides on the workbook, the workbook no longer has to be saved to capture the data. This means the transformation functionality can be provided by a read only Template Workbook. (A Template Workbook is a workbook which fully separates data from associated transformation processes).
As the Template Workbook is marked as read only, it is readily sharable with other users, there is no danger of corruption by a user saving an overtyped or modified formula being encountered by other users. Any enhancements or bug fixes to the workbook are done in a single copy and these changes become instantly available to all users.
The architecture allows a user to save his input and (calculated) output data as PersisTables. Reloading existing copies of PersisTables input data allows the outputs to be compared with previously calculated values, and hence a comprehensive testing regime can be created. As results are always obtained from a ‘static’ Template Workbook (which is only changed to reflect desired system improvements or enhancements), modifications can be monitored to confirm only expected changes have occurred.
This approach eliminates a range of common EUC problems resulting from a workbook being ‘a copy of a copy of a corrupted copy’.
It is obviously important to continue to observe good practice regarding system security, testing etc of Template Workbooks.
Copyright © 2020 Persis Solutions Limited