Excel Assembly (libraries and books)

  Beginning with Pathagoras 2018.1, it is possible to assign an Excel spreadsheet as a text source to a 'book' in a Pathagoras library and to a DropDown List.  The spreadsheet must be structured in a way that Pathagoras recognizes the essential attributes of a documents ('name', 'subject' and 'text'), but the rules are easy to follow: the first column of the spreadsheet contains the 'name' of the clause. The second column contains the (optional) 'subject' (if no subject is provided, Pathgoras will use the text in column 1 as the subject). The third column contains the actual text that Pathagoras will use as the 'document text.'

  Many users have already told us that creating clauses in Excel for document assembly is easier than creating clauses in Word, because the three attributes -- name, subject and text -- are all simultaneously visible. But because of other limitations -- primarily formatting and auto-paragraph numbering -- it should not be viewed as a perfect substitute.

   The Process:

1.Open a new spreadsheet. Name the first three cells of the first row 'Name', 'Subject' and 'Body Text,' or something similar. (The names are not critical, but the words you use will help future authors and editors in making changes.)

2.In the appropriate column add the appropriate value. The 'name' you insert in the first column typically should be short, the 'subject' a bit longer and more descriptive; and the body text can be any length up to 32,000 characters.

You don't actually have to 'type' anything. Copy and pasting from other sources works just fine. Except for maintaining the 3 column structure, Pathagoras doesn't care how you move things into the spreadsheet. Copying and pasting is just fine, including copying and pasting tables. Excel recognizes Word tables.

3.Save the spreadsheet wherever you wish, with any name that makes sense to you. Like other Pathagoras tools, Pathagoras points to your files. We never demand that you put anything in a particular spot. (And,yes, you can store it on a network drive so it is accessible to all users.)

4.Assign the spreadsheet to a 'bookshelf'. See Adding Book to Library for the steps. The 'I want my book to be an Excel spreadsheet' choice is (currently) the last item in the list after you click the 'Document Assembly | (Empty Bookshelf) | Quick Set' sequence.

5.Alternatively (actually, in addition), you can assign the spreadsheet to a DropDown List. See these pages. Be sure to choose the '. . . an Excel spreadsheet' as your choice. (The other Excel choices allow you to import Excel data, as opposed to text blocks.)

   When you are ready to call in a term, follow the identical steps you use to call in text from any other source. It's pretty much point and click. No navigation, since the location of the source file is already built into the program after you follow the above 'pointing' steps.

   If you are using classic Document Assembly (the third button in the Pathagoras toolbar), select the book from which you wish to draw text. Click the Next button. Pathagoras quickly reads and presents to you all the names and subject of the clauses store in the Excel file. Choose the ones you want, moving them from left to right. Reorder if desired using the up and down arrows. Click Next. Pathagoras will then present 3 options: (1) insert the text as 'plain' text, (2) insert each text block in a cell (building a Word table within the document or (3) insert not only the 'column 3 text' text but multiple columns associated with each selected term. (More on multi-select later. For now just select one of the top two and watch the action.


Please Note: This is not a 'forms fill-in' routine. It's true document assembly. Other programs that integrate with Excel can fill in pre-formatted static forms by transferring data such as names addresses quantities colors into designated spots on the form. That's NOT what Pathagoras is doing. We are literally assembling clause text stored in Excel into complex contracts, letters, transactional documents and reports.

A few examples of Excel Assembly spreadsheets:

Click to enlarge.

Sample list that a psychologist might create to allow a quick insertion of psychological aspects of a patient (behavior, moods, affects, etc.)



Click to expand

Sample Interrogatories. This example shows that multiple lines are possible.
The '#." inserted at strategic locations allow automatic numbering when transferred into Word.



Excel Assembly (tests)

Another psychology example. IQ and personality tests (abbreviations, full names and descriptions).



   Setting up Excel Assembly is where Pathagoras plain text 'chops' shine. You can add any Pathagorizing markers to your Excel text. All of those markups will be transferred intact -- because they are plain text!! -- from Excel to Word. So, square brackets identify [variables]. Curly braces represent {simple optional text}, Robust <<*Options* . . . >>, <<*Optional* . . .>> and <<*Repeat* . . .>>; <<document calls>> they all transfer intact into Word and are immediately processed as if they started from Word in the first place. (This power and flexibility is simply not possible with other programs that are field-dependent. You cannot transfer fields from Excel into Word.)

   Just like with folders and glossaries, with Excel Assembly you can display either the 'Name' or the 'Subject' in the Clause Selection screen to identify to desired target text. Regardless of which you display, when a selection is made, it is always the text in the third column that is assembled into your document. (See, however, the below section titled Multi-column selections for a newly added variation on this.)

Limitations (and some workarounds):  

   Formatting in Excel is limited. Font, colors and emphasis are available, but these characteristics don't always carry forward. (The do if you you select the 'Keep Tables' mode or you use the Multi-Column selection. However:

Insert a "#." at the beginning of a line and Pathagoras will add automatic paragraph numbering.

Insert "<c>" at the beginning of a line, Pathagoras will automatically center that line.

Insert "<b>" at the beginning and end of a block of text, Pathagoras will automatically bold the text within those boundaries.

Ditto with "<i>", but italics.

To the extent you need more extensive formatting, just create the desired text in a Word document and save it (more about where to save it later).  In the Excel spreadsheet, use a <<document call>> in lieu of (or in addition to) body text. A <<document call>> is Pathagoras way of finding any text in any document and inserting that text in place of the <<document call>>. You can use a fully qualified reference to the document you want to insert, e.g., <<c:\practice areas\estate planning\trusts\Family Trust.docx>>. If the document being called is in the folder in which the Excel spreadsheet resides, or if the document is located in any DropDown List, or is in the SuperFolder, you can just type the documents name within the angle brackets, e.g., <<Family Trust>>. Pathagoras will find it.


Multi-column selections:

   You can have up to 9 columns of assembly-able text. While column 3 is the 'document text' (to draw a parallel between document assembly and Excel Assembly, we offer addition tools to take advantage of the power of Excel. If you use the multi-column technique, Pathagoras will assemble the selected items into a table in your Word document.

   Caveat: If you need more than 9 columns. only the first 9 can be accessed by this routine.) Perhaps  want to create a catalogue of parts that is tailored to a particular customer.

Excel Assembly (parts)

   If you choose the Multi-column option, you can select the rows (i.e., names) and the specific columns you want to insert into your Word document. However, only Word tables can be create, not 'prose'. Given the nature of the source text and the likely desired result, we do not see this as a drawback. However, your thoughts are solicited on the best use of this tool.


  DropDown Lists and <<Document calls>>: If you intend to call text into Excel text via a DropDown List  via a <<document call>>, only the text in column 3 (think of it as the 'document text') will be inserted. (If your spreadsheet is 4 or more columns and you wish to insert text other than what is contained in column 3, you must use the Document Assembly tool (with the spreadsheet assigned as a 'book.')

Created with Help & Manual 7 and styled with Premium Pack Version 2.70 © by EC Software