Bookmark this page Print this page

Spreadsheet, Example Templates

You will not be able to work with templates in Excel until you are familiar with Mailshot Selection in Durell (i.e. to select which records should be sent to the template) and Data Sets (i.e. to specify which data fields should be included for those records). For example you might want to select all of your clients with a post code starting "TA" (this is the Mailshot Selection) where you wish to include their names, addresses and mobile phone numbers in the spreadsheet, but not their work phone numbers (this field selection, and also the order in which these fields appear, is controlled by the Data Set). Essentially the Mailshot Selection provides the rows of the spreadsheet while the Data Set controls what's in each column, and the order of these columns (e.g. the "Address line 1" column before or after the "Address line 2" one). The problems with producing a basic spreadsheet (or "data dump") like this are...

1) The column headings are automatically populated with the names of the fields selected for the Data Set, like "Reference", instead of the titles you may prefer, like "Policy Ref"
2) The data is unformatted
3) No data manipulation is included, for example to add the values of columns 1 and 2 and display them in column 3

These issues can be resolved by using an Excel template, and the following help will show you how to create and use one to list policy, claim or client details.

NOTE : If you have an existing layout (e.g. a bordereaux specified by an insurer) that you'd like to incorporate in your copy of Durell but find the following help a bit too daunting, then provided you can create the Data Set to establish which fields you need (i.e. to match the column headings) and ensure they're sequenced in the corrrect order, then Durell's Support Team should be able to develop the final template for you for around £100+VAT (see Data Set tips at the end of this Help section).

Creating an Example Template

An overall spreadsheet is also known as a "work book", which may have numerous sub-spreadsheets referred to as "worksheets". For clarity this example uses three such worksheets, so open your Excel with a new workbook and then right-click and rename the three "worksheet" tabs at the bottom left (see below) as "Control", "Report" and "Data", where...

Then click on the "Report" tab to view that worksheet and add the titles shown below. Note that in this example we'll be listing policy records, where the associated client data may be repreated in a number of rows (i.e. because one client may have many policies). 

 

Save this very basic template as an "Excel Macro-Enabled Template (*.xltm)" in the TEMPLATE sub-folder of your main Durell data folder called IMW-DATA (e.g. as S:\IMW-DATA\TEMPLATE\Example Spreadsheet.xltm), and note that any such templates saved in this location will thereafter automatically be available in your Durell Document Viewer. Also note that you need this to be "macro-enabled" (i.e. to be an *xltm file) in order to automate formatting.

Next starting from your Policies program in Durell, open the Document Viewer to start a new spreadsheet as shown above, selecting your new "Example Template". Then use the "Setup" button next to the "Data field set" dropdown to create a new data set called "Example Data Set", as shown below, where you'll need to clear the tick from the box re "If ticked list fields by..." so that you can select them in the correct order to match the template. Note that the first three fields come from the "Client Details" table, while the last four are from the "Policy - Details 1" table. When you have all seven fields selected in the correct order click "Save" then "Close". For further help with the selection of data fields see the tips at the end of this Help section.

Make sure the "Example Data Set" now appears in the middle of your "Link to Microsoft Excel" pop-up, as shown above, then finally select the dropdown to "Place data into an existing Worksheet", which will offer the three worksheet names you setup for your template, and select the one for "Data". The illustration above shows the "Select records" set to "Policies, all records", which will suffice for this example, though typically you will start the whole process by first making a mailshot selection of your policies, and then using this dropdown's option to select "Policies, mailshot = Y".

After clicking "OK" you should find the raw data on the "Data" worksheet (see below), although the spreasheet will probably open on the "Control" one. In this illustration the first row has been changed to bold face to distinguish it from the data - but you don't need to do this. The next step will be to create a macro that'll move this raw data into the Report worksheet, under the preferred titles, and optionally with other formatting and formulae. Then finally we'll add a button on the "Control" worksheet to run this macro and hence automatically generate the Report.

 

As you can see in the above spreadsheet there is a "Developer" tab, though there wasn't one in the previous screenshot showing the Report titles, because this tab is not displayed by default. To make it appear in Excel just click "File / Options / Customise ribbon" and set the "Developer" tick-box (middle right). Now back on your "Control" worksheet with the Developer" tab selected, do the following...

1) Click the "Record Macro" button, then give your macro the name "CopyData", plus an optional description like that shown below, then click its "OK" button to start recording.

2) Click the "Data" tab to switch to that worksheet, then click on the first item of data (e.g. "Mr J Bell" in cell A2, above)
3) Hold down your "Shift" and "Ctrl" keys then tap the "End" key to go to the end cell of your data
4) With all of the data now highlighted hold down the "Ctrl" key and tap key "C" to copy it into your computer's memory
5) Click the "Report" tab at the bottom left of your screen to move to that worksheet
6) Click on the cell at the top left below your first title (to re-position your cursor to the cell where you want "Mr J Bell" to appear)
7) Hold down the "Ctrl" key and tap key "V" to move the data onto the Report worksheet
8) Click the cell at the very top left corner (containing a triangle symbol between cells "1" and "A"), so that the entire worksheet is selected (i.e. highlighted)
9) Click the "Home" tab, then the "Format" dropdown and select "Autofit Column Width"
10) Click the little blue button at the bottom right of the entire spreadsheet to stop the macro from recording

At this point you should have copied the raw data from the "Data" worksheet without its titles onto the "Report" worksheet below the latter's titles, and then formatted the columns to fit the data. Obviously you could have kept going with the macro, for example to hide the Excel headings from your report by first clicking the "Page Layout" tab, then clearing the ticks from Headings (n.b. the macro at the end of the Help section includes a line to do this). You might also have included extra commands to get the total or average values of selected columns, etc. The principle is the same.

The final step in this Example Template exercise is to create one or more buttons on the "Control" worksheet to run your macro or macros (because other users may not have their "Developer" tab displayed from which they can run macros). To do so, first click the "Control" tab to move to that worksheet, then...

We now need to save the overall revised spreadsheet (with its new macro and button) so that it becomes the final version of your template. Before doing so clear all the data on the "Data" worksheet, and everything except the titles from the "Report" one. Then click "File / Save as" and save it again as an "Excel Macro-Enabled Template (*.xltm)", just like you did originally in the TEMPLATE sub-folder of your main Durell data folder called IMW-DATA. Be warned that Microsoft will almost certainly change the filepath when you select "Excel Macro-Enabled Template", because it'll assume you want these in the folder called C:\Users\YourName\AppData\Roaming\Microsoft\Templates rather than your Durell one called S:\IMW-DATA\TEMPLATE. You should see the original version when you're saving, in which case you can click on it to get the exact same file name, as shown below...

Now close Excel and use Durell's Document Viewer to re-select and run the template as before, though this time when it opens on the "Control" worksheet you'll be able to click your "Run Report" button to do all of the work.

Note that when such a template (*xltm) is opened from Durell's Document Viewer it will always generate a new spreadsheet (i.e. an *xlsx file) as opposed to editing the original template. You can expressly choose to re-save it as a template in order to further change the underlying macros, etc, where your macros can be viewed and edited via the "Macros" button on the "Developers" tab. But by default it'll always save as a new spreadsheet.

The final macro for the Example Template is shown below with added comments in green...

DATA SET TIP 1 : You may not know what a particular data field is called, or in which table to find it (e.g. "Policy - Details 1"). A useful tip for resolving this is first to populate a policy (or client or claim) record with a lot of different values and a Search Name or Reference Number like "AAA", "DUMMY POLICY" or "TEST CLAIM" so that other members of staff don't mistake it for a live one. Then you should enter easily identifiable values in the fields that conern you, e.g. "99999.99" as its premium. Next create a Data Set called something like "Test" (so others realise its contents may change) and for all of the possible tables (e.g. "Policy - Details 1" to "Policy - Special info") quickly select all of the field names (using the top ">|" button to instantly select all fields in the current table). Then save this "Test" data set and starting from your dummy client, policy or claim record, use the Document Viewer to create a basic Excel spreadsheet for just the "Current record" using this "Test" data set. Then use the "Find" option in Excel to locate your value (e.g. 99999.99) and note the data field name above it (e.g. "PolDets_Premium").

DATA SET TIP 2 : Another useful tip when searching through a Data Set's list of  "Select fields" or "Selected" ones, is that if you press the probable first letter of the field's name (e.g. the letter “R” when looking for fields related to renewals) you’ll jump to the next one starting with that letter, skipping the ones in between. For example …