Import Data Using Templates
Templates represent reusable data mappings between fields in the WIP report and cell ranges in a custom sheet. The source cell ranges may be horizontal (e.g., A1:Z1) or vertical (e.g., A1:A30). Templates are similar to the data mappings described in Import Data Using Data Mappings. However, unlike data mappings, templates are reusable across multiple workbooks, because they are saved as independent files (not inside the workbook like data mappings). With templates, it is therefore possible to import data from virtually any source workbook that contains the cell ranges predefined by the template.
A significant difference between data mappings and templates is that cell ranges referenced by a data mapping are fixed (predetermined), whereas cell ranges referenced by templates can grow if more rows or columns of data are available in the source. Likewise, they can shrink if fewer rows or columns are available in the source. Unlike data mappings, all mapped cell ranges in a template must come from the same workbook and worksheet, as further explained below.
Create a template
You can create a new template as follows:
1.Open any existing Excel workbook which contains WIP data in custom (not XBRL-bound) format.
2.In the WIP tab, click Insert and add a blank WIP report into the workbook. See also Create a new WIP Report.
3.In the WIP Report Pane, click the Data Mapping tab (see screenshot below). Notice this tab has three other tabs: In Process, Completed, and DEI (Document Entity Information). The first two tabs let you map fields belonging to the In Process and Completed sheets, respectively. The DEI tab allows mapping data for the fields which appear in the document properties, such as the start and end date of the reporting period, Registrant Name, and others.
4.Enter a cell range next to each field in the WIP report that you want to be part of the template. Type the cell range or click the Ellipsis button (in the latter case, the range will be filled in automatically). Then select the range from the source sheet. For example, the image below illustrates the field Contract Identifier mapped to the cell range B3:B7 of Sheet1 (see screenshot below). The cell range selection mechanism is the same as for Excel formulas. For more information, see the Microsoft documentation.
5.Once you have mapped all the required fields to cell ranges, click Save Template. At this stage, validation takes place. If no error messages appear, the following dialog box is displayed:
6.Optionally, select a destination template folder. By default, the template folder is C:\Users\...\Documents\Altova\WIPAddInForExcel. You can optionally change this folder to another one by clicking the (Ellipsis) button.
7.Enter a file name in the File name box. The template will be saved to the selected template folder as a file with a .wdmt (WIP Data Mapping Template) extension.
8.Optionally, enter some descriptive text in the Template name box, which will help you identify this template later (e.g., Template 1).
If your WIP worksheet already contains previously defined data mappings (as explained in Import Data Using Data Mappings), it is possible to save them as a template. This means that you can go directly to Step 5 above. All data mappings must comply with the rules for referencing cell ranges (see below). Otherwise, errors may occur, and the template will not be saved. Templates are meant to be reusable and not bound to specific workbooks like data mappings. Therefore, when you save data mappings as a template, any references to specific workbooks are removed. Only references to worksheets and cell ranges will be preserved by the template. For example, a cell range such as [CustomBook.xlsx]CustomSheet!\$C\$4:\$C\$8 would be saved only as CustomSheet!\$C\$4:\$C\$8.
Rules for referencing cell ranges
The rules below explain how to reference cell ranges.
•All cell ranges must consist of either single rows (e.g., A1:K1) or single columns (e.g., A1:A20). In other words, all of them must be vertical or horizontal.
•All cell ranges must come from the same worksheet of the same workbook.
•All cell ranges must begin and end in the same row (or column in the case of vertical ranges). For example, an error will occur if the field Contract Identifier uses the range B1:B10 and the field Contract Name uses the range C2:C9. However, no error will occur if these fields use the range B1:B20 and C1:C20, respectively.
When you apply the template to a workbook, more or fewer rows or columns may be available in the source workbook than specified by the template cell range. In this case, the cell range will grow or shrink to match the exact number of rows or columns.
Edit existing templates
Templates saved previously can be modified as follows:
1.Open any workbook that contains an XBRL-bound WIP Report sheet.
2.Click the Data Mapping tab in the WIP Report Pane.
3.Click Load Template.
4.Select the required template from the list of available templates and click Load. If the templates were saved to a custom template folder, click the (Ellipsis) button to switch the template folder.
5.Edit the cell ranges in the Data Mapping tab, if necessary. The same rules apply as when adding new templates. See Rules for referencing cell ranges above.
6.Click Save template.
Apply a template to a custom WIP workbook
Once you have created a template, it can be applied to any source workbook that contains cell ranges defined by the template. To apply a template to a custom WIP workbook, follow the instructions below:
1.Open any custom Excel workbook that contains cell ranges defined by the template.
2.In the WIP tab of the Excel ribbon, click Insert. Notice that the right-hand area of the dialog box below displays any open Excel workbooks and their worksheets (in this example, the workbook CustomBook.xlsx and the sheet CustomSheet).
3.Select the template name (in our example, Template1) in the Custom section. Any template files available in the Template Folder appear in this dialog box automatically. If no template files appear in the dialog box, make sure that the template folder contains .wdmt template files that you have created previously (see Create a template above). To switch the template folder, click the (Ellipsis) button.
4.Click the In Process or Completed tab, depending on the kind of WIP report you wish to import data into.
5.Click Open Workbook and browse for the Excel book from where you would like to import data. You can ignore this step if the source Excel workbook is already open. In this example, the source workbook was already opened in Step 1. Therefore, this step can be ignored.
6.Select the worksheet from which you would like to import data. In our example, the data is imported from the CustomSheet worksheet of the workbook CustomBook.xlsx.
7.Under Mapping Method, select either Copy or Reference (see explanation below) and click OK.
There are two ways to import values from a source sheet: copying or referencing them. Copying means that the value is literally taken from the source range and placed into the target. If the source value changes, it is not automatically updated in the target WIP sheets. However, you can update the WIP report from the source at any time by clicking the Copy Values button. Referencing means that, instead of storing an actual value, the WIP sheet will contain a formula which refers to the source cell range. Updating the value in the source will also automatically update it in the target sheet next time when you open the target sheet.