Template workbooks

Sometimes, workbooks or worksheets are used as templates for multiple data sets.A common example of this is where a workbook is used to prepare quotations for different customers. Copies of a standard worksheet are then used to quote for different options or components of the whole, and the entire workbook is then saved with the data for that customer. The problem arises when there is a need to update the format of the workbook. The new format can be used easily enough for new quotes, but it is quite difficult to use it to update existing quotes, as there is no easy way to transfer the data from the old workbook to the new.

This is another situation where it is helpful to separate the data from the processing (i.e. the quote template), however in this case our approach is different. We have developed software which allows the data to be stored in xml format, either in external files or inside the workbook using Custom Xml Parts (a feature introduced into Excel with Excel 2007). This allows multiple data sets to be saved from/loaded into the same worksheet, and all the data to be exported to/imported from an external xml file. In this way it is easy to update the master workbook and re-import any of the saved data sets. The main features are:

A data map is used to define the locations in the workbook where the data is to be stored, providing considerable flexibility.
An additional ribbon tab shows the data sets available within the workbook and allows the user to load and save the data.
It is also possible to save 'template' data sets which can be reused and modified in individual cases.
A user-defined function provides access to any data stored within the workbook, which is particularly useful for preparing summaries.
It is possible to define multiple independent groups of data sets within a single workbook.