You are here

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

Research articles : 
 

Excel spreadsheet that writes data from Excel to Word document forms.

Designed for Windows computers only, not Macs.

Download for $9CAD from https://legaltree.gumroad.com/l/mcisc 

 

Introduction to the Form Builder Spreadsheet
 
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:
  • setting up Word document template forms;
  • creating matter sheets within the Form Builder Spreadsheet; and
  • writing data from the Form Builder Spreadsheet to the Word document template forms.
 

 
Security issues
The spreadsheet was developed using macros (programmed in visual basic) and in order for the spreadsheet to operate your security settings in Microsoft Excel must be set to allow macros to run.  To enable macros in Excel 2016 (similar procedures will apply in other versions of Excel) click:
  • File (i.e. the menu on the top left)
  • Options
  • Trust Centre
  • Trust Centre Settings
  • Macro settings
  • Enable all macros
Macros can pose a security risk because they make your computer perform certain steps automatically, but are typically only dangerous when made to be that way by a computer programmer with a malicious intention – hence the warning when you change the security settings in Excel. However, the macros available in the spreadsheet available for download on this page are made with good intentions and should not harm your computer.
 
Users downloading and using this spreadsheet do so entirely at their own risk and agree to accept all risks associated with using the spreadsheet. 
 
 
Word document template forms
 
Create .docx files with any content wanted, plus field codes
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.
 
Formatting of field codes is maintained when writing in data
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.  
 
Do not use curly brackets except in field codes
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. 
 
Store forms in subfolders
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:
  1. Library folder in a suitable location.
  2. Collection of subfolders, suitably named, containing the forms.
 
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).
 
Back up libraries of forms
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.
 
 
Enter form library paths on the “Inputs” sheet
 
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.
 
 
Overview of a typical matter worksheet (litigation lawyer data assumed)
 
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:
  1. Buttons on Add-ins menu. Gives access to buttons that provide various functionality. Macros must be “enabled” in Excel for the Add-ins menu to appear. Note that macros must be enabled in Excel for the Add-ins menu to appear, and for the macros in the Form Builder Spreadsheet to run.
  2. Form selection listboxes. This is where the form to be written to is selected.
  3. Date input area. This is where the date to be written into the {DateDocument} field contained in many forms is specified.
  4. Table for input of variables. Allows multiple variables (e.g. name, address, telephone number, email address) to be specified for a list of people (e.g. recipients of a letter). Typically this area is used to store contact information for all contacts involved on the client matter. The blue shaded cells are for field codes also entered in Word, and the green shaded cells are for the corresponding variables values.
  5. Variables in list format. This is a further area to list off field codes used in the Word document, and to specify their corresponding variable values. Again, the blue shaded cells are for field codes also entered in Word, and the green shaded cells are for the variables values.
 
Form selection area and overview of Form Building Process
The form selection area allows one to choose which Word document form will be populated with data from the spreadsheet:
  1. Use the first listbox to select the library of forms that contains the form to be used.  The libraries of forms are those inputted on the “Inputs” sheet as discussed above.
  2. Click the grey button below the first listbox to update the content in all listboxes i.e. that macro looks through the library and imports the latest form data from all subfolders in that library.
  3. In the second listbox, select the category (subfolder) in the library that the desired form is in. To display (in the third listbox) all forms in all categories (subfolders) then click “1 – ALL FORMS” at the top of the list in the second listbox.
  4. In the third listbox, select the desired form.
  5. If the list of forms in the third listbox is long then type part of the form name into the grey cell below the third listbox to narrow down the list displayed in that listbox.
  6. Click the grey “Preview Form” button to preview the selected form.
  7. Click the green “Prepare Form” button to run the macro to populate the form. This macro will take note of all variables listed in blue cells on the sheet, and all corresponding variable values in the adjacent green cells on the sheet, and then open the Word Document form and write in all variable values in place of the applicable field codes. If a field code in the Word document does not have a variable value entered in the spreadsheet, that field code will simply be removed from the Word document i.e. it will be replaced with a blank.
 
Date input area
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.
 
Input of variable codes, and variable data (List section)
 
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.
 
Field codes, and the corresponding variable values, can be specified on matter sheets in table format, or list format. The list format section is simpler and so is discussed first.
 
The below screenshot shows the top portion of the zone of a matter sheet for entering variables in list format:
  1. Variable codes are entered in column B (shaded blue, confirming such field codes are also entered into the Word document forms).
  2. Descriptions of variables can be written in column C (this data is not used elsewhere, but this is just a place to describe the variable, or include other notes).
  3. Variable values are entered in column D (shaded green, confirming that is the data is entered in the Excel spreadsheet for writing to the Word document forms).
 
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.
 
Root Folder Path setting is mandatory
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.
 
Input of variable codes, and variable data (Table section)
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:
  1. If a form such as the above is used but fewer than 8 addressees are involved the macro will simply delete those excess field codes and leave white space which can be deleted.
  2. In the above sample template Borders are shown in the Word document because that facilitates deletion of unused table cells after the macro has written data into the form, but as always it is up to the user to design their Word document forms as they wish.
  3. The {AttentionX} variable allows one to write in “Att:” when the individuals name is included as part of an address.
  4. The {RoleX} variable allows one to specify the role of the recipient e.g. “Counsel for the defendant XYZ Ltd.”
 
Matter Sheet Template, and creating sheets for new matters
 
Enter standard data onto Matter Sheet Template
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.
 
Two methods to create a matter sheet  
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:
  • clicking the “Create sheet for new matter” on the Add-Ins menu; or
  • going to the “Sheet Nav & Table Data” tab and clicking the green “Insert Sheet” button.
 
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.
 
 
Sheet Nav & Table Data sheet
 
The “Sheet Nav & Table Data” sheet can be thought of as the “home page” of the Form Builder Spreadsheet and sheet serves two functions:
  • Provides tools for navigation between matter sheets, and adding and removing matter sheets.
  • Contains a master table of data which can be sent to specific matter sheets. Typically the table will contain address data, but that need not be the case (any type of data could be managed in the table).
Navigation tools
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.
 
Master table of data
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:
  • Search in selected column: Click on a column to search in, and then click this button and a window will come up that allows one to search / filter the data in that column for specific text. This is a convenient way to jump to a specific address once the list of addresses becomes long.
  • Sort by selected column: Used to sort the data in the table of contacts into alphabetical order. Can be useful when updating the addresses for multiple individuals in an organization that has an address change i.e. sort by the organization column and then edit and copy/paste the new address for all contacts at that organization.
  • Send selected contact to selected sheet: Click on the row (in the table on the “Sheet Nav & Table Data” sheet) for the contact to be sent to the matter sheet specified in the listbox at the top of the sheet, and then click this button to send the selected contact to that selected matter sheet. 
Rows can be added to or deleted from the table of data using the normal functionality in Excel, or by using the “Add Table Row” and “Delete Table Row” buttons in the second column of the Add-ins menu:
 
 
The “Add Table Row” and “Delete Table Row” buttons can also be used on matter sheets.
 
Sending data from matter sheets to “Sheet Nav & Table Data” sheet
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.
 
 
Writing data to the Word document template
 
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.
 
Mismatch between Word doc and Excel sheet is permitted
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.
 
Checking for mismatch between Word doc and Excel sheet
Users may wish to check:
  • what field codes are in a Word document form but that are not included at all on the matter sheet in Excel, or
  • what field codes in a Word document form are listed on the matter sheet in Excel but which have no variable value specified.
For this purpose the following macros are provided:
  • The “Check for missing field codes in Excel” macro at the top of the fifth column of the Add-ins menu can be run to check for field codes in the selected Word form that do not exist on the matter sheet in Excel.
  • The “Check for missing variable values in Excel” macro on the second line of the fifth column of the Add-ins menu can be run to check for field codes in the selected Word form that do not have variable values specified on the matter sheet in Excel.
 
Preparing a form
When ready to prepare a form, proceed as follows:
  1. If the list of forms is long, consider typing some letters in the name of the form into the “Enter text to narrow down form list” field (cell D19 in the screenshot below) to narrow down the list of forms. This step is optional and only helpful when the list of forms is long (not the case in the screenshot below).
  2. Select an appropriate form from the “Select form name” listbox.
  3. Click the “Preview Form” button to preview the selected form. This step is optional. Note that the “Preview Form” button opens up the master copy of the form from the library so exercise caution when making edits and saving changes.
  4. Click the “Prepare form” button to write the data from the matter sheet in Excel into the Word document form. The macro will end by presenting a “Save As” dialog window allowing the form to be saved. As discussed above, the macro will prompt to save the form at the path specified for the {RootFolderPath} variable near the top of the Variables in List Format section of the matter sheet.
 
Follow the “Save As” prompt provided at the end of the form writing macro
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.
 
Optional check of whether table data specified
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:
  1. Using the listbox on the Inputs sheet specify whether checks should be run “Always”, “Never”, or only “When form is contained in one of the folders listed below”.
  2. If the “When form is contained in one of the folders listed below” option is to be used, the subfolders containing forms that the check should be run for must be listed in the yellow shaded cells below the listbox:
 
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.
 
 
Additional commands on the “Add-Ins” menu
 
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.
 
Create Subfolders
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.
 
Open folder for matter
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: