While we encourage you to use as many Word tools as possible to avoid having to jump between applications, there simply is no better tool for creating tables, inputting lengthy data sets and for making complex calculations than Excel. Excel makes it easy to set up elaborate and quite attractive tables. It allows you to manipulate and validate data in ways not possible with Word or any other program.
This routine is not for 'data' importing, i.e., scraping data in an Excel 'intake form.'
The routine discussed below is for importing whole tables from Excel into Word.
Pathagoras, for its part, makes it easy to transfer that data, those tables and those charts into Word.
The previous pages discussed how to import a single table or two into a Word document using a DropDown List, and how to scrape (import) data contained in an Excel spreadsheet from single cells into an Instant Database.
But if you need to import a more than just a table or two (some customers have dozens) into your Word document, this is the page to read.
Conceptually (as well as in practice), the process quite simple:
1. After you have constructed a table in Excel, give the table a name. To name a section of an Excel spreadsheet, highlighting the group of cells you want to 'name' (in this case it will be the table). In the 'Name' block (upper left corner of the spreadsheet), type a name and press Enter. The name must be a single word (or two or more words connected by underscores). The name should not exceed 33 characters.
2. Insert a 'document call'. Using a pair of angle brackets as the boundaries, type <<Import_(name of table)>>, where (name of table) is the name of the table as you assigned it in Excel. (Following the above example, the document text would read "<<Import_Cost_Analysis>>".)
3. An alternative to the document call to insert a bookmark where you want the table inserted. The first part of the bookmark name must be 'Import_'. The second part of the bookmark name must match the name of the table as you assigned it in Excel. So if you named the Excel table 'Cost_Analysis', the bookmark name in Word would be "Import_Cost_Analysis".
Note: Excel names and Word bookmarks must be 'single' words. No spaces and no special characters. The exception to the 'no special characters' rule is the underscore which can be used to joint words to make them 'one.' There is a 40 character limit to Excel Names and Word Bookmark names. (Excel cell and area names used in this function must be 33 or fewer characters because of 'Import_' preamble required for bookmarks.)
When Pathagoras processes the document, it determines if there are bookmarks in the final document which begin with 'Import_". If any are found, Pathagoras assumes that you are calling on the program to insert an Excel table into the document. Pathagoras will then ask you (1) to select the spreadsheet contains the tables you want to copy over and (2) whether the inserted table should or should not retain a link back to the spreadsheet. (If you anticipate updating the text or the calculations, then you should link back. If the calculations are final and/or you plan to distribute the document to other, you should not maintain the link-back). Pathagoras then opens the selected spreadsheet, finds the named are of the spreadsheet that matches the bookmark, data' (stripping away the 'Import_' command) and copies and pastes the spreadsheet into the document at appropriate location.
Pathagoras lets you pre-select the spreadsheet, or a path to a collection of spreadsheets, and indicate whether the link-back should be maintained. Here are some examples.
<<Import_Cost_Analysis*Quotes*Link>> inserts and preserves link to the 'Cost_Analysis' named section of the Quotes.xlsx spreadsheet located in the default Excel folder.
<<Import_Cost_Analysis*Quotes.xlsx>> ditto, but program will ask if you want to maintain link back
<<Import_Cost_Analysis*L:\Projects\Spreadsheets\Quotes.xlsx*Link>> inserts and preserves link to the 'Cost_Analysis' named section of the Quotes.xlsx spreadsheet located in folder 'L:\Projects\Spreadsheets'
<<Import_Cost_Analysis*L:\Projects\Spreadsheets\>> inserts the 'Cost_Analysis' named section of a spreadsheet to be selected. Pathagoras will display 'L:\Projects\Spreadsheets' folder for you to select specific spreadsheet.
An Excel lesson: Expanding tables
We normally don't teach how to use other programs, but this is an important tool to know, and to use, if you use Excel data frequently.
An expanding table is like a spreadsheet within a spreadsheet. It can occupy as little or as much of the parent spreadsheet as you need. It typically starts out as a relatively small section of the spreadsheet, but as you add more data, it expands as needed.
So if you are listing bank accounts for a client ("BoA","checking" $1,000"; "Fidelity", "IRA", $20,000", etc.), or creating a list of personal property items in the home and their respective values ("sofa" "$50"; "dining room table", $75", etc), each time that you add a new item to table, the table expands. The math, if equations were provided, is automatically performed and any 'named' range grow as the table grows. It truly is the 'best of all worlds."
To create an expanding table, just highlight an area in your spreadsheet that contains your data (including any formulas). Click the Insert tab and then the 'Table' button. That's it. Play around with it to get the feel of the action.
A secondary (but important) benefit of a table within a spreadsheet is how your cursor moves when you hit the tab key. Once the edge of the table is reached, it bounces back to the beginning of the next row. If you are at the bottom of the table, Excel creates a new row for you automatically . . . that's the expanding part.