OLE linking
Object Linking and Embedding (OLE) is a widely used Microsoft technology that enables objects in two applications to be hot-linked, so that changes to the object in one application cause the same changes in the other application. For example, by linking an array in Analytica to a table in a Microsoft Excel spreadsheet, any change to the array in the Analytica model is automatically reflected in the spreadsheet.
By using OLE linking, results from Analytica models can be linked into OLE compliant applica- tions like Word and Excel. Linking data can save a great deal of work because it saves you from performing repeated copy and paste operations between Analytica and other applications when- ever your model results change. Without OLE, if you copied result tables from Analytica, pasted them into a Word document, and later you tweak your model results, you would need to re-copy and re-paste all those result tables. However, if you link those tables using OLE, all the data in the Word document either updates automatically, or if you prefer, when you explicitly decide to update the data.
You can link any of the result table views (i.e., Mid, Mean, Statistics, Probability Density, Cumula- tive Probability, and Sample table views). You can link any two-dimensional slice of a multi-dimen- sional table with the regular Copy command. For result tables with more than two dimensions, you might decide to link the entire table as a series of two-dimensional tables using the Copy Table option from the Edit menu. You can also link a rectangular region of cells that are a subset of a a two-dimensional table. However, you cannot link non-table data such as the information that is contained in the Object window or Attribute panel.
Linking Procedure
Steps for linking result data from your Analytica model to an external OLE-compliant application are as follows. For concreteness, we’ll assume here that the other application is Microsoft Excel.
- In the Analytica Result window, select the cells you want to link and choose Copy from the Edit menu (Control+c).
- From Excel, select the cells where you would like the Analytica data linked.
- From Excel, choose Paste Special from the Edit menu.
- The Paste Special dialog appears.
- In this box, choose the option Paste Link, select Text from the As list, and click OK.
You’re done. Any changes to the source result table are propagated to the linked data in Excel.The procedure for linking Analytica model results to other OLE-compliant applications is similar to the above steps.
Detailed Example of Linking Analytica Results
This example itemizes detailed steps for linking an Analytica result table into an Excel spread- sheet. Suppose you would like to link the model results displayed above into an Excel spread- sheet. You can start by linking the column and row headers. Go to the node titled Cashflow Category and evaluate its result. Notice the result of node Cash Flow Category is displayed as a column of cells, but you would like to have them linked into Excel as a row. Unfortunately you can- not link this data as a row with a single Copy/Paste Special operation since Excel does not let you transpose the linked data from a column to a row. However, you can easily work around this limi- tation. Link the values into an unused portion of your spreadsheet or to a blank sheet using the linking procedure described in the previous section. In the cells where you actually would like the labels to appear as a row, simply reference the linked cells. In other words, define the cells that comprise the column headers for the linked table you are creating using the names of the corre- sponding linked cells. Now it’s time to link the values of Time as the row headers in your linked table. Time is an Analytica system variable and one of the elementary ways to copy its values for linking is to create a node called Time and give it the definition time. Evaluate this node and then link the values dis- played in the result table using the linking procedure described in the previous section.
Linking the body of the table is just a straightforward application of the linking procedure. The number format of the cells is preserved in fixed point format, but you might want to use Excel for- matting to get the dollar sign and thousand separator displayed. Excel might switch to the expo- nential number format or display ######## if your columns are not wide enough.
The body of the table and its indexes (the row and column headers) are linked. For instance, if your Analytica model results change and you decide also to change the value of cost to expense, these changes are reflected in your linked table in Excel.
Important Notes about Linking to Analytica Results
Changing File Locations
When moving linked files from one drive partition to another on the same machine or between two different computers, keep the relative paths the same. The simplest way to do this is to keep the linked model files and the other application files to which they are linked in the same folder.
Automatic vs. Manual Updating
OLE links are set for automatic updating by default, but you can change this setting to manual. We recommend this if the data is linked from an Analytica model with a lengthy re-computation time or to an application with a lengthy re-computation time.
To Change a Link’s Setting to Manual in Word
- On Word’s Edit menu, select Links.
- In the Links box that appears select the link(s) you’re interested in adjusting.
- Click the radio button labeled manual and click the OK button.
In other OLE-compliant applications the steps for switching from automatic to manual updating should be very similar to the ones listed above.
You can also decide to set all your OLE links to be updated manually using a preference setting in Analytica. From the Edit menu, select Preferences, then in the Preferences dialog, uncheck the checkbox located on the bottom right labeled Auto recompute outgoing OLE links.
Using Indexes
Array-valued results that are to be linked should not have local indexes (created using the Index..Do construct). All indexes should correspond to index nodes in your diagram.
Number formatting
When linking data into OLE compliant applications, the number format is the same as Analytica’s format at the time of link creation. However, if the linked Analytica data uses the default Suffix number format, the linking converts the format to Exponential, which is more universally recogniz- able in other applications. In programs that have their own number formatting settings such as Excel, the number format is likely adjusted according to the settings for the cells you are pasting into. However you must still be careful about losing significant digits (see next paragraph).
Precision is another important issue in number formatting. Before linking from Analytica, you should first adjust the number format so that it displays all the significant digits you would like to have in the other OLE-savvy application to which you are linking.
Refreshing Links when Analytica Model is Not Running
If you refresh the links between an Analytica model and another OLE-savvy application when the Analytica model is not running, the following events occur:
- A new instance of Analytica launches.
- Analytica loads the model.
- Analytica evaluates the variables upon which the links are dependent.
- The links reactivate.
- The linked data updates.
There are two ways to refresh the links this way. The first case occurs when a file with links is opened while the model file to which it is linked is closed, and you answer Yes to the dialog prompting you to update the linked data. The other way is if you are working with a file containing links to a model that is not running and you explicitly update the links. To explicitly update the links in Excel, you would select Links from the Edit menu. Then in the Links dialog, select the links you would like to refresh and click the Update button.
Enable comment auto-refresher