Commercial Complete - Contents http://www.commercialcomplete.com -


Inserting Links to Excel Spreadsheets

    Functionality described in this section requires CC Pro Plus 1

Commercial Complete Overview 


 

MS Excel Links - Requires CC Pro

Follow the steps on the graphic below this text to insert an MS Excel link in your document.  When a link is inserted as an Updatable Link, changes made in the Excel file and saved will be reflected in the Commercial Complete document when its Fields are updated.  

 

MS Excel MUST be Installed on the Computer

In order to insert links to MS Excel spreadsheets, the MS Excel program must be installed on the computer and the version of MS Excel must be capable of loading the spreadsheet that you are trying to link.  Both xls and xlsx files can be linked, but to link xlsx files, the installed version of MS Excel must also be capable of loading xlsx files. 

 

Cells in the Excel Spreadsheet Must Be Named

Before an area of an Excel spreadsheet will appear on the list of Name Cells or Ranges, a name must be assigned to the cell or range of cells.  This is done in MS Excel by highlighting a cell or range of cells, clicking the Insert button, then Names, then Define.  A name can also be assigned by highlighting a cell or range of cells and entering a name for the range in the Name Box that is usually displayed near the upper left corner of a worksheet.  Once the names are assigned and the Excel file is saved, the names will appear in the list of Named Cells and Ranges.

 

Opening the MS Excel Links Window

To open the MS Excel Links window to insert an Excel Link or to change the path of the Excel files that will be used for a property record, first bring the desired property record into "Current View".  This is done by displaying the record in the Data Entry View.  Usually, this is the record that will be the Subject of your current analysis.  

 

 

Follow the steps in the graphic that is presented at the bottom of this Help Topic if you want to insert a link in a document.  If the Commercial Complete document that you will be using already contains Excel Links and you just wish to set the correct file paths for the new Subject, change the paths at the top, click the dropdown to the right of the Insert Links button (bottom right of window) and select the second option.  This option reads: "Do Not Insert Link - Save Edits to Excel Link Files and Close.

 

Excel Files 1, 2, and 3

As many as three different Excel files can be associated with any property record and then data and/or updatable links from those files can be inserted.  For example, an Excel file containing a special income analysis, one containing demographics and statistics, and a third containing zoning information all can be linked to a specific property record and data from those files can be inserted in your document.

 

Insertion Format

A named cell or range can be inserted as a Graphic, as Text, or as a Table.  As a general rule, we recommend that the spreadsheet be designed in such a way that the linked cell ranges to be inserted are formatted to align within the margins of a printed page.  For example, if the named cell range has 6 columns, the column widths should be such that those columns will fit within the margins of a printed page.  Fonts should be set accordingly. If this rule is not followed, certain cell ranges that are inserted into a Commercial Complete document as a Table will extend beyond the physical page and will be very difficult to reformat in Commercial Complete.  A wide cell range can be inserted as a Graphic, in which case it will be resized automatically to fit within the page margins.  However, the resizing can result in difficult to read text.

 

 

Retain Formatting

Check this box to retain the text formatting in the Excel spreadsheet.  When unchecked, the font name and font size at the cursor position of the Commercial Complete document will be used.

 

Display Gridlines

Check this box to display the gridlines as they appear in the Excel spreadsheet.  This option is only effective when a cell range is inserted as a Graphic.  

 

Method of Insertion

Check this box to display the gridlines as they appear in the Excel spreadsheet.  This option is only effective when a cell range is inserted as a Graphic.  

 

 

 

Automating Documents

When an Excel link is inserted as an Updatable Link, only certain information is stored in the document.  This information includes the Name of the cell or range from the spreadsheet, the format of the link (Graphic, Text, or Table; retain spreadsheet format, and whether to display gridlines).  

 

The number of the Excel File is also saved in the document (ExcelLinkFile1, ExcelLinkFile2, or ExcelLinkFile3).  The actual location of the file on your disk is NOT stored in the document.  It is stored in the property record.  This makes it easy to attach a new property record to a document that already contains Excel Links and update the document and its links with a different property record (usually a new Subject).  But in order for this to work, you must associate new Excel files with EACH new property record that will be used with an automated document that contains MS Excel Links.  It is essential that the new Excel files contain the same named cells or ranges that are contained in the inserted links.  Otherwise, the following message will be displayed:

 

Excel Link Unavailable-Link File Missing or Not Specified 

 

Once the Excel Files 1, 2, and 3 are selected, the name and location of the files on your disk will remain with the property record until changed.  If the files are later moved, their data will no longer be available to Commercial Complete documents, so take care before moving an Excel file that is associated with a property record.  If you do move an Excel file, be sure to return to the Excel Links window and assign the new location to the property record. 

 

Use MS Excel Links with Care

It is your responsibility to understand the relationship between the MS Excel links inserted in the Commercial Complete document and the Excel files.  An understanding is essential if you are to successfully automate documents with MS Excel links.  Because most, if not all of the fields that are needed in a document are contained in each Commercial Complete property record, we recommend that you use MS Excel links sparingly and only as required.  

 

Document automation between different programs is wonderful - when it works.  But we all know the stories of "broken links" when trying to link Excel files with MS Word documents.  Even when functioning properly, linking between programs slows down document automation significantly because two different programs are always competing for limited computer resources (in this case Word and Excel or Commercial Complete and Excel).  Both must be running at the same time.  This also can result in temporary memory conflicts that can "crash" both programs or result in a "freeze" of one or both programs.  You know those stories too.

 

It is not necessary to rely on Excel links when fields like Property Name, Street Address, Sale Price, and 250+ other fields are already available for insertion directly from Commercial Complete property records.  Commercial Complete fields never get "broken" and Excel does not have to be running in the background.  The reliability of your automated document increases dramatically.  The fewer MS Links you use, the better.  That way, even if an MS Link somehow gets "broken", it is very easy to just insert the link again and move on, following the steps in the graphic below.

 

 

Inserting MS Excel Links - Step by Step