Excel Text Assembly (libraries and books)

  While Excel is typically used as a 'data' source to complete your document variables, an Excel spreadsheet can also be an excellent source of substantive text for document assembly purposes. When properly structured arranged (discussion below) the spreadsheet It can be assigned to a 'book' in a Pathagoras library or to a DropDown List. When so assigned, it is indistinguishable from other text sources.  

  To accomplish this 'text source' role is easy. It's just a 3 column setup, mimicking the essential attributes of a documents ('name', 'subject' and 'text').  So, the first column of the spreadsheet would contains the 'name' of the clause. The second column would contain the (optional) 'subject.' (If no subject is provided, Pathagoras will use the text in column 1 as the clause's 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.

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 in one of your Libraries. See Adding Book to Library for the steps. The 'Excel spreadsheet' choice is (currently) the last item in the list after you click the 'Document Assembly | 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 here. We are literally assembling clause text stored in Excel into complex contracts, letters, transactional documents and reports.

A few examples of Excel Assembly spreadsheets:

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

Click to enlarge.

 

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

Click to expand

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

ExcelAssembly(tests)

 

Notes:

   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 into Word. So, square brackets identify [variables]. Curly braces represent {simple optional text}, Robust <<*Options* . . . >>, <<*Optional* . . .>> and <<*Repeat* . . .>>; <<document calls>> 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 used in other programs 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 carried into your document. (See, however, the below section titled Multi-column selections for a newly added variation on this.)

    Just like with other 'books,' you can assign a template to an Excel book. Before assembly is started, the template is laid down and the selected clauses are poured in. Unless otherwise designated, all text comes in with 'normal' style. The <t> and <h> commands will (appropriately) respond to the template settings.

Formatting:    

   Microsoft made formatting in Excel intentionally limited. There is just no real need for it when used natively. Font, colors and emphasis are available, but these characteristics don't always carry forward into the assembled document. (They do if you you select the 'Keep Tables' mode or you use the Multi-Column selection.) But (again showing off 'plain text' possibilities, you can easily note formatting requirements using the following simply additions:

Automatic paragraph numbering: Insert a "#." at the beginning of the text in column 3. After assembly, Pathagoras will add automatic paragraph numbering.

Center text: Insert "<c>" at the beginning of the text in column 3. After assembly, Pathagoras will automatically center that line.

Bold: Insert "<b>" at the beginning and end of a block of text you wish bolded. After assembly, Pathagoras will automatically bold the text within those boundaries.

Italics: Ditto with "<i>", but italics.

Headers: Insert "<h>" or "<h1>" or "<h2>" at the beginning of a line. After assembly, Pathagoras will assign the appropriate header style to that line.

Tab: Insert "<t>" anywhere in the text. After assembly, Pathagoras will insert a tab at that spot.

Entire Word Documents: 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>>. 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. Column 3 is the 'document text', but you can use the 'multi-column' option to 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.)

   Example: Perhaps  want to create a catalog of parts that is tailored to a particular customer.

ExcelAssembly(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 created, 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 using a DropDown List  via a <<document call>>, only the text in column 3 (think of it as the 'document text') will be inserted. (So, 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.')