Form Builder Spreadsheet - Document assembly / automation using Microsoft Excel and Word

 

Repeatedly typing the same information into different Word document forms is time consuming, and often results in errors. The Form Builder Spreadsheet is a tool designed to streamline the creation of commonly used Word documents forms by storing key data in an Excel spreadsheet and then writing it as necessary into Word document “forms” (a.k.a. templates, or precedents). The forms regular Microsoft Word documents but have variables inserted wherever information relating to the particular client matter / project file will be written in.

 

In the legal context the matter data contained in the Excel spreadsheet could be names and roles of the parties to the dispute, court file numbers, registry locations, lawyer details, etc. which will be repeatedly used on court forms and other documents.

 

Note how variables are denoted in the Word document by curly brackets and then those same variables are listed in the Excel spreadsheet, along with the values for those variables for the particular matter.

 

While setting up precedent forms requires some up front effort, efficiencies are gained once the library of forms is created. Instead of perpetually copying and pasting, or retyping, data into precedent forms full of blanks, one can enter key data just once on a matter sheet within the Form Builder Spreadsheet and then write it to various forms as needed.  

 

Risk reduction (avoid mistyping information) is another key benefit of the Form Builder Spreadsheet, but care must still always be taken to proofread, and add final data to, forms before final sign off and use.

 

The below describes the process of:

 

 

• File (i.e. the menu on the top left)
• Options
• Trust Centre
• Trust Centre Settings
• Macro settings
• Enable all macros

 

 

 

The Form Builder Spreadsheet requires Word document precedent forms, which must be .docx file format Word documents (i.e. not the older .doc format). Those templates must contain “field codes” at the places were data is to written in. In this context, the word “field" indicates a space where content is to be added, and the word “code” confirms that special characters are needed to demarcate the boundaries of the space in the form where the content is to be added.

 

To build forms, simply create a Word document containing the text the form should contain and, at the locations to contain matter-specific-data, type in the desired text description of the field code, and then wrap that text in curly brackets (a.k.a. braces, or squiggly brackets) e.g. {WhatEverTextYouWant}

 

The screenshot below shows a hypothetical “Cheque Request Form” which could be created in a Word document and which has field codes for data to be entered from the matter sheet in the Form Builder Spreadsheet:

 

As a further example, the following screenshot shows the field codes used in the style of cause for a hypothetical court document:

 

If the Word document contains a field code but the matter sheet in the Form Builder Spreadsheet does not contain a value for that field code then the field code in the Word document will simply be overwritten with blank text (effectively being deleted) when the macro runs. Depending on the layout of the field codes in the Word document form some deletion of white space may be required after the form is written.

 

Any number of field codes can be used, so long as they are wrapped in curly brackets, and are also entered on the matter sheet in the Form Builder Spreadsheet (further discussed below).

 

Note that one can specify multiple instances of the same field code in many places throughout the Word document form. The macro will keep searching for repeat instances of each field code in the Word document form until no more are found, and then move on to the next field code.

 

The field codes in the above examples are shown without spaces e.g. {CLAIMINGGROUPNAMES} rather than {CLAIMING GROUP NAMES}. Although spaces are permitted in field codes omitting spaces makes the field codes more visibly noticeable in the Word document.

 

The content written into the field codes will take on the formatting that was applied to the field codes in the Word document forms. For example, because the names of the parties on the above court document should be in caps in the finished Word document, those field codes in the Word document form are entered in caps.  

 

Note that form templates should NOT use curly brackets anywhere other than at the beginning and end of field codes. Using curly brackets other than in field codes will confuse the macros (the macros assume all curly brackets indicate field codes). Curly brackets can be added to the Word documents forms after running the macros that write data to the form template. The Form Builder Spreadsheet merely writes data into Word document templates and afterwards the Word document can be edited, saved, and otherwise dealt with like any other Word document. 

 

The Word document forms must be stored within folders on a Windows computer.

 

The Form Builder Spreadsheet can access multiple libraries of forms, but each library must have all of its forms in first level subfolders, and not in sub-subfolders, etc. In other words, the library structure must be as follows:

 

The Word document forms cannot be directly in the library, and cannot be in sub-subfolders in the library. These restrictions arise from the way form libraries are accessed using the Form Builder Spreadsheet (see further below). 

 

The Form Builder Spreadsheet itself need not be saved in any particular library (or any particular location at all), but will know where to find the Word document forms by the path(s) of the form document library(ies) being listed on the Inputs sheet of the Form Builder Spreadsheet (see further below).

 

It is good practice to routinely make a backup Word document form libraries in case a form is accidentally overwritten by being saved after data is written to it. If data is written to a form and it is then “saved” (rather than using “Save-As”) then the template form will be overwritten (destroyed). The macros in the Form Builder Spreadsheet are set up to discourage accidental overwriting of forms, but users who dismiss the prompts to “Save As” and then absent mindedly later “Save” may obliterate (by overwriting) precedent forms. Making frequent backups is (as in all contexts) prudent.

 

 

 

The first sheet in the Form Builder Spreadsheet is named “Inputs” and allows specification of various information used by the spreadsheet.

 

At the top of the Inputs sheet is a space where up to 10 paths can be specified for Word document form libraries. The path of any library of forms to be used can be copied and pasted into one of those 10 lines.

 

 

Other parts of the Inputs sheet are described below when discussing the features of the spreadsheet they relate to.

 

 

 

The following sections describe the different parts of a typical matter sheet. A hypothetical litigation file of Smith v. Jones is described to aid the explanation.

 

A separate worksheet is created in the Form Builder Spreadsheet for each separate matter e.g. if a lawyer takes on a matter for a new client that client matter will have its own matter sheet in the Form Builder Spreadsheet.

 

As shown in the screenshot below, each matter sheet has the following key areas:

 

The form selection area allows one to choose which Word document form will be populated with data from the spreadsheet:

 

Many Word document Forms (e.g. letters, documents filed in court, etc.) should state the date the document was created. The {DateDocument} field code, shown in cell B23 of the screenshot below, should be treated as a “fixed” field code that can be used in Word document forms and into which the date in cell C23 will be written:

 

 

As shown in the screenshot above, one can use the =NOW() formula in cell C23 to ensure it always contains the current date, such that the date the form is prepared will be written to the {DateDocument} field codes in the Word document.

 

Excel handles dates different from other data and the {DateDocument} field is the only field designed to handle data formatted as a date. Other data that presents as date values can be written into field codes in the Word document forms, but such other data entered in the Excel sheet should begin with an apostrophe such that it is treated as plain text by Excel.

 

 

Variable codes, also called field codes, and their corresponding variable data values, must be entered on the matter sheet in Excel so that the macro that runs in Excel knows what field codes to look for in the Word document form, and what data to write over the field codes in the Word document form.

 

 

The below screenshot shows the top portion of the zone of a matter sheet for entering variables in list format:

 

Users generally have wide freedom to invent their own field codes, but must always ensure the field codes used in the Word document forms match those in the blue zone of the matter sheet the form will be generated from.

 

Users can use other Excel functionality such as listboxes to control variables values e.g. for the field code in cell B61 in the screenshot above the variable value in cell D61 will be written into the Word document form, but the macro does not care how that variable value got into cell D61. In the example above the listbox at rows 63 – 66 defines what value is entered in cell D61. In this example it allows the user to quickly select different “re” lines (to be used in letters or other correspondence) from the range of options entered in cells C63 and below. This reinforces the concept that the macro simply searches the Word document form for the field codes in column B and writes over them using the corresponding variable values in column D, and how that variable value got into the relevant cells in column D does not impact the macro.

 

The {RootFolderPath} shown in cell B43 of the above screenshot is an important mandatory field code that must have (in its variable value field in column D) a valid path to a folder that will serve as the default save location for all forms for the matter. As the form building macro finishes running it will prompt the user to save the form at that default location, and one can drill down into a deeper subfolder as needed.

 

The Form Builder Spreadsheet will often be used to write data for persons with multiple data points e.g. letter recipients will have an individual names, organization names, addresses, etc. To facilitate this the Form Builder Spreadsheet has not only a simple vertical list of variables (described above), but also a table of variables. The table is found above the list section discussed above, and has the following appearance:  

 

 

This discussion refers to the situation of many addressees on correspondence because this is an easily understood example, but any type of data could be entered in the table.  

 

The column headings for the table of variables are specified on the “Sheet Nav & Table data” sheet, and those column headings on the “Sheet Nav & Table data” sheet should be carefully planned at the outset because those variable names will be written in as table column headings each time a new matter sheet is created from the Matter Sheet Template (this is further discussed in the section below that explains the “Sheet Nav & Table data” sheet).

 

The table variable names shown along the top of the table all end with “X”, denoting that the field code will be matched with a numeric value entered in the left column of the table of data on the matter sheet. For example, a Word document form may contain the field code {IndvidualName1} and because a 1 is entered in cell B30 (see screenshot above) the name “Greg Jones” from cell D30 will be written into the Word document in place of the text {IndvidualName1} in the Word document. In other words, the “X” in the table column heading is replaced with the numerical value found in the column on the left of the table, and the resulting field code is what the macro searches for in the Word document form. Note that the Xs are not entered in the Word document form but rather the field codes entered in the Word document form should end with numerical values that match with the numeric values entered in the blue column at the left end of the table i.e. do not enter {IndvidualNameX} in the Word document, but enter {IndvidualName1}, {IndvidualName2}, {IndvidualName3}, etc.

 

Entering number data in the left column of the table of variables is important i.e. unless there is number data for the macro to substitute into the place of the “X” in the column headings no complete field codes will exist on the matter sheet for the table data and as a result no table data will be written into the Word document form.

 

Additional rows can be added to the table using the normal “insert row” functionality of Excel and as many sequential numbers entered in column B for various recipients. But remember that the field codes defined by the column headings will be combined with the numbers in the left column of the table, and those combined codes will be what the macro searches for in Word. For example, if a letter will have 8 recipients then the combined code will be {IndvidualName8} and the Word document form must contain that field code e.g. as is done in the following letter template for sending a letter up to 8 recipients:

 

 

The following points about the above sample form may apply to other forms as well:

 

 

Each matter will have its own unique data to be written into forms e.g. client name, matter number, various address info, etc. will be specific to the matter. Therefore each matter should have its own sheet within the Form Builder Spreadsheet. The sheet named “Matter Sheet Template”, which has a tab with blue shading, is used as a template when creating new matter sheets.

 

 

Edits / modifications can be made to the Matter Sheet Template, but keep in mind that whatever is on the Matter Sheet Template will, by default, appear on all subsequently made matter sheets. Therefore, set up the “Matter Sheet Template” with generic information that will apply to all matters, and apply any matter specific edits after generating the particular matter sheets.

 

Each “Author” of documents created using the Form Builder Spreadsheet should have his/her own Form Builder Spreadsheet used by that Author. Indeed, part of the efficiency of the Form Builder Spreadsheet comes from one Author’s data (name, phone number, etc.) being entered on the Matter Sheet Template such that the same data does not have to be entered each time a new matter sheet is created. Different authors can share the same library of template forms, but each author should each have his or her own Form Builder Spreadsheet. 

 

Note how the blue column headings cells for the “Variables in table format” portion of the Matter Sheet Template are empty, as shown in the screenshot below.

 

 

When the Matter Sheet Template is used as the foundation for a new matter sheet the headings from the table on the “Sheet Nav & Table Data” sheet will be written in those column heading cells i.e. do not add content to those empty cells on the Matter Sheet Template but edit table column headings as needed on the “Sheet Nav & Table Data” sheet (discussed further below). Likewise, the pink warning text contained in the screenshot above will be deleted when new matter sheets are created from the Matter Sheet Template and so there is no need to remove that text from the Matter Sheet Template.

 

Note that various cells on the Matter Sheet Template (and the resulting matter sheets created from the Matter Sheet Template) are named ranges and those names should not be removed because doing so will interfere with the operation of the macros. Exercise caution before deleting rows or columns because that may result in deletion of the named ranges in those rows or columns.

 

Once the Matter Sheet Template is set up and populated with field code data that will be used for all matters, one can go ahead and create a matter sheet by either:

 

When the macro is run to create a new matter sheet a pop-up window will ask for the name to be used for the matter sheet, and a name not longer than 31 characters can be entered.

 

 

 

The “Sheet Nav & Table Data” sheet can be thought of as the “home page” of the Form Builder Spreadsheet and sheet serves two functions:

After matter sheets are created they will be listed in the listbox in column B of the “Sheet Nav & Table Data” sheet (only one shown listed in the screenshot below):

 

Select a matter sheet in the listbox and click the orange “Go to Sheet” button to go to the selected matter sheet.

 

Once the list of matter sheets in the listbox becomes long, searches (for any string of letters anywhere in sheet names) can be done in cell B3 to pare down the list of sheets in the listbox to just those sheets containing that string of letters. For example, “jo” was entered in cell B3 in the screenshot above which filtered down the list to sheets with “jo” (not case sensitive) anywhere in their names, which in this case was just the “Smith v. Jones” sheet. Click the grey “Clear” button in cell C3 to reset cell B3 to empty, or select cell B3 and press the Delete key to achieve the same result. Once cell B3 is emptied the listbox will display the full list of matter sheets again.

 

The yellow “Refresh Listbox and hide all matter sheets” button can be clicked to rebuild and display the full list of sheets in the listbox, and to hide all matter sheets. This declutters the workbook before opening the matter sheets to be worked on next. Repeatedly selecting matter sheets in the listbox and clicking “Go to Sheet” will open up additional matter sheets but will not hide already displayed matter sheets i.e. additional tabs will be added for newly displayed sheets and will accumulate until the Refresh button is clicked to clean things up by hiding all matter sheets.

 

The “Delete Sheet” button can be used to delete matter sheets that are no longer needed i.e. select the sheet to be deleted in the listbox and then click “Delete Sheet”. Note that this button deletes the sheet currently selected in the listbox without any further cautionary warning, and the deletion cannot be undone.

 

The lower portion of the “Sheet Nav & Table Data” sheet contains a table for storing data. Although the table is not restricted to storing address data the following explanation refers to that as an example because it is a common use for the table.

 

The table can be used to store addresses that may be used on multiple matters, or that one may otherwise wish to keep on record for future use even after a matter sheet is deleted:

 

 

The headings of the table on the “Sheet Nav & Table Data” sheet will be defined when first using the Form Builder Spreadsheet (i.e. before creating the first matter sheet) and should be defined carefully as those headings will be copied in as the headings for the table of variables on all subsequently created matter sheets.

 

As indicated in the screenshot above, each headings of the table must end with an “X” before the close curly bracket. Recall that when table data is used on a matter sheet the X in the variable name (as indicated by the column heading) is replaced with a numerical number (as entered in the column at the far left of the table on the matter sheet) and the form writing macro searches for that completed field code in the Word document form. But a key point is that all table headings must end with an “X” before the close curly bracket.

 

The three blue buttons at the top of the table perform the following functions:

 

 

The “Add Table Row” and “Delete Table Row” buttons can also be used on matter sheets.

 

The “Send row to Table Data sheet” button at the bottom of the third column of the Add-ins menu (see screenshot above) can be used on any matter to sheet to send a row of table data from the matter sheet to the “Sheet Nav & Table Data” sheet e.g. if a matter is finished and the matter sheet will be deleted but certain addresses may be useful in the future then those lines can be written to the “Sheet Nav & Table Data” sheet for later use.

 

In some cases issues with duplicate contact information on the “Sheet Nav & Table Data” sheet could arise e.g. address information is sent to the “Sheet Nav & Table Data” but similar address information is already listed there. To allow a check to be done for similar existing data already on the “Sheet Nav & Table Data” sheet one can specify up to two columns that duplicate checks will be done for. For example, if the data is address information the user may wish to specify that the {IndividualNameX} and {OrganizationNameX} fields should be checked for existing matches on the “Sheet Nav & Table Data” sheet. Since those are field code columns “2” and “3” (counting from the left) on the “Sheet Nav & Table Data” sheet, the figures “2” and “3” should be entered in the available spaces on the Inputs sheet:

 

 

The above check for duplicates already on the “Sheet Nav & Table Data” sheet are optional, and those input cells on the Inputs sheet can simply be left empty if checks are not desired.

 

 

 

Once the field codes used in the Word document form have been entered on a matter sheet, and suitable corresponding variable values also entered on the matter sheet, forms can be prepared.

 

It is not necessary for all field codes specified in the Word document form to be listed on the matter sheet, and any field codes that do not appear on the matter sheet in Excel will simply be removed from the Word document form when the “Prepare Form” macro is run.

 

Similarly, field codes which are listed on the matter sheet in Excel but do not have to have corresponding variable values specified will simply be removed from the Word document form when the “Prepare Form” macro is run.

 

Users may wish to check:

 

When ready to prepare a form, proceed as follows:

 

It is strongly recommended to follow the “Save As” prompt shown after the “Prepare Form” macro runs and go ahead with saving the newly created form to a suitable folder. If one clicks “Cancel” when prompted to save then the master copy of the template form (but now with matter specific data written into it) will continue as the active document. If the user then later clicks “Save” (rather than “Save As”) the original form template will be overwritten / destroyed. This is why the user should not “Cancel” when prompted to “Save As” as the macro finishes, and is also why, as noted above, it is advisable to make backups of form libraries.

 

Experience shows that populating the numbers in the blue column at the left end of the table of variables before preparing forms is something that users sometimes forget.

 

While there can be benefit to the macro alerting the user to the fact that no number info has been entered in the left column of the table, such warnings can also be annoying if no table data is to be used for the particular document being created. Settings on the Inputs sheet allow users to specify whether or not the macro should provide warnings if no number info is specified in the left column of the table of variables. Adjust those settings on the Inputs sheet as follows:

 

When the check is set to run and no numbers are found in the left column of the table of variables on the matter sheet then a pop up warning will be displayed asking the user if the macro should proceed even though no number data is entered in that column. The user can choose to dismiss the warning and proceed, and the macro will run, but with no number info in the left column of the table no data from the table of variables in Excel will be written into the Word document form.

 

 

 

In this section the function of certain buttons on the Add-Ins menu are described, but no explanation is provided for buttons already discussed above, or for buttons whose functionality is obvious.

 

Note that if the mouse cursor is hovered over any button on the Add-Ins menu a tooltip will be displayed providing a brief description of the function of the button.

 

This spreadsheet is designed for use on Windows computers and it typically each matter that has a matter sheet in the Form Builder Spreadsheet will also have a folder in Windows containing documents related to that matter. It may be the case that a consistent subfolder structure will be used in each matter folder and the “Create Subfolders” macro button in the fourth column of the Add-Ins menu allows a user to write a pre-defined set of empty folders into the root folder specified for the {RootFolderPath} field code:

 

 

The library of subfolders to be written into the root folder can be three levels deep and is specified on the Inputs sheet:

 

 

If any subfolders already exist in the folder tree, and whether or not such pre-existing subfolders contain documents, that content will not be disturbed by the macro.

 

To allow quick navigation to a Windows folder that the matter sheet relates to users can click the “Open root folder” button on the Add-ins menu, and that will open the path specified for the {RootFolderPath} field code: