OLE linking


Analytica User GuideIntegration with data and applicationsOLE 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, if you use OLE to link an array in Analytica to a table in a Microsoft Excel spreadsheet, any change to the array in the Analytica model automatically updates the table in the spreadsheet immediately, or the next time you open the spreadsheet. Conversely, you can link a spreadsheet table to an Analytica Array, so that any change to the spreadsheet propagates automatically to the Analytica model. OLE linking saves the effort of the repeated manual copy and paste operations you would otherwise have to do to keep the spreadsheet and Analytica model in sync whenever the source data changes.

Linking Data from Analytica to an OLE-Compliant Application

You can set up OLE linking from or to any of the result table views (i.e., Mid, Mean, Statistics, Probability Density, Cumulative Probability, and Sample table views). You can link any two-dimensional slice of a multidimensional table with the standard Copy (ctrl+C) command. For result tables with more than two dimensions, you can 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 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.

  1. In the Analytica Result window, select the cells you want to link and choose Copy from the Edit menu (Control+c).
  2. From Excel, select the cells where you would like the Analytica data linked.
  3. From Excel, choose Paste Special... from the Edit menu to open the Paste Special dialog.
  4. In this dialog, choose the option Paste Link, select Text from the As list, and click OK.

That's it! Any changes to the source result table are propagated to the linked data in Excel. The same or similar steps work to link from Analytica to other OLE-compliant applications.

Tip
The external application must support OLE-linking of tab-delimited text data. Applications that do not support this format do not display “Text” as an option in Step 5 above, or disable the Paste Special menu item in Step 3.

Example of Linking Analytica Results

18.1.png

Here is an example of the steps to link an Analytica result table into an Excel spreadsheet. Suppose you would like to link the model results displayed above into an Excel spreadsheet. You can start by linking the column and row headers. Go to the node titled Cash Flow 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 cannot 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 limitation. 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 corresponding 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 displayed 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 formatting to get the dollar sign and thousand separator displayed. Excel might switch to the exponential 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.

18.2.png

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 files in the same folder.

Automatic vs. Manual Updating

By default OLE links update automatically. You can change this setting to manual. We recommend this if the data is linked from an Analytica model (or other application) that takes a long time to recompute.

To Change a Link’s Setting to Manual in MS Word

  1. From MS Word’s Edit menu, select Links.
  2. In the Links box that appears, select the link(s) you’re interested in adjusting.
  3. Click the radio button labeled manual and click the OK button.

The steps to switch from automatic to manual updating should be very similar in OLE-compliant applications .

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. If the data uses Analytica's default unique Suffix number format, the linking converts the format to Exponential, which is more universally recognizable 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:

  1. A new instance of Analytica launches.
  2. Analytica loads the model.
  3. Analytica evaluates the variables upon which the links are dependent.
  4. The links reactivate.
  5. 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.

Linking Data from Other Applications into Analytica

Using OLE Linking, you can incorporate data originating in OLE-compliant applications as the input for nodes in your Analytica model. You accomplish this by linking the external data to edit tables in Analytica. Once again, this removes the need to perform numerous copy and paste operations each time the source data in the other application changes.

When linking data into Analytica, you can link data into any edit table with less than three dimensions. When linking data in edit tables you must link all the contents of the table; linking a subset of an edit table is not supported. You cannot link data from other applications to anywhere else than an edit table in Analytica including the diagram windows, object windows, and the Attribute panel.

Linking Procedure

Steps for creating a linked edit table in Analytica with data from an Excel spreadsheet:

  1. In Excel, select the cells you want to link to Analytica and choose Copy from the Edit menu.
  2. In Analytica, make the edit table where you want the Excel data linked the front-most window.
  3. From the Edit menu or the right mouse button pop-up menu, choose Paste Special. The Paste Special dialog appears.
  4. In this box, choose the option Paste Link, select Text from the As list, and click OK.

The process for linking data from Word or other OLE-compliant applications are analogous to the steps just outlined.

Example of Linking a Table into Analytica

Here is an example of the steps for linking a table from Excel into Analytica by creating a node with a “Linked Table” definition. Specifically, suppose you desire to link the Excel table displayed in the following figure into Analytica.

18.3.png

Start by creating two indexes in Analytica to store the row and column headers. Title the first index Items and the second Status. Select the node Items and then click the Show definition button on the toolbar (this is the button with the pencil icon) or right mouse menu. In the Attribute panel or Object window that appears, click the expr popup menu and choose List of Labels. Press the down-arrow or Return key three times. This gives you three cells — item 1, item 2, and item 3. In Excel, copy the three cells used as the row headers (i.e. Red Widgets, Blue Widgets, and Green Widgets); return to Analytica and do a regular paste into the three cells of the definition for the Index node Items.

Now you need to copy the values of the column headers (i.e., In Stock and Ordered) into the definition for the index node Status. Since Analytica enforces strict dimension checking (i.e., you cannot paste a 3 x 1 array of cells into a 1 x 3 array of cells), you are required to first convert the row into a column. You can accomplish this easily by copying the row, moving to an unused portion of the spreadsheet or onto a blank sheet, and choosing Paste Special from Excel’s Edit menu. The Paste Special dialog appears and you need only select the Transpose checkbox on the bottom right. Click the OK button and you have converted the column header cells from a row into a column. Now copy this column, go back to Analytica, select the Status node, and click the Show definition toolbar button. Select the first cell item 1 and choose Paste from the Analytica’s Edit menu.

Since you’ve finished creating the indexes, you’re ready to start on the node that contains the linked table. Create a variable node in Analytica and title it Inventory. With this node selected, click the Show definition button on the toolbar. In the Attribute panel or Object window that appears, click the expr popup menu and choose Table. The Indexes dialog appears. In this dialog, select Items and click the ▼ button. This moves Items to the Selected Indexes section. You also want to select Status and then click the ▼ button to make it a selected index as well. Click OK and an edit table appears as follows.

18.4.png

Go to Excel and select the numerical values displayed in the table and choose Copy from the Edit menu (Control+c). Return to Analytica (while in edit mode) and click anywhere in the edit table grid. Choose Paste Special from the Edit menu and the Paste Special dialog comes into view. You want the settings in the box to be Paste Link and Text which are the default settings (see below). Click OK.

18.5.png

The caption for the table changes from Edit Table to Linked Table and you’re done. If you arrange the application windows so that you can see the source table in Excel and the linked table in Analytica, you can readily demonstrate that the link is activated. Change the value for Green Widgets Ordered from 2 to say 17. The corresponding value in Analytica’s linked table changes accordingly.

18.6.png
Tip
The data within the table is linked and is updated automatically when altered, but the row and column headers are not linked and any changes to their values must be propagated using the standard cut and paste operations. Perform this by copying to the indexes used by the table, not to the table itself.

Important Notes about Linking into Analytica Edit Tables

Changing File Locations

When moving linked files on the same machine or between two different computers, keep the relative paths the same so that the files can locate each other. The simplest way to do this is to keep the linked model file(s) and the other application file(s) to which it is 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.” This might be desirable if the linked data is used in a model with a lengthy computation time.

To Change a Link’s Setting to “Manual” Updating:

  1. On Analytica’s Edit menu, select OLE Links.
  2. In the Edit Analytica Links box that appears, select the link(s).
  3. Click the radio button labeled manual and click the OK button.

Terminating Links

You might want to terminate a link to a source file for a number of reason including if you do not have the source file or if you would like to edit the values in a linked table. To break a link, bring up the Edit Analytica Links dialog, by choosing OLE Links from the Edit menu. Select the link you would like to terminate and click the Break Link button.

Activating the Other Application

If you have linked data from an external application into Analytica, after loading Analytica you can make the other application visible using the Open Source button on the OLE Links dialog, accessed through the Edit menu. If you implement a portion of your model in Analytica and a portion in an external application, with OLE links in both directions, you can make both applications simultaneously visible on the screen by loading the Analytica model first, then pressing the Open Source button to open the external application.

Links that Adapt to Size Changes

OLE links to or from Excel tables usually have fixed size -- i.e. a fixed number of rows and columns. If you later insert or delete rows or columns, as is easy in Analytica, OLE can't handle this, so you must remove and redefine the OLE link with the modified table.

However, there is a way to define a link from Excel into Analytica that automatically adjusts its size when rows are inserted or deleted in Excel. You can see how to perform this trick in This video (6 minutes, 6 MB, requires Windows Media Player).

Changing Link sources Programmatically with Moniker attribute

You can dynamically change sources for OLE links through typescript code -- for example, as a result of pressing a button in the Analytica model. This makes it possible to automatically adjust link sources from a button in response to a structural change in the spreadsheet without forcing the user to redefine the OLE link. To change the data source of an OLE link, set/change the Moniker attribute of the edit table. For example:

(Moniker of DataFromExcel := "C:\Temp\WBook.xls!Sheet1!R3C4:R5C8")

The Moniker source can, in general, be computed, which creates much flexibility.

You can create a link in this way by setting the Moniker attribute for the first time. Be careful, if you are linking a table with 2 or more dimensions, to ensure that the linkedReform attribute is set to indicate the "pivot" for the table. If you don't have the pivot right, the size may not match. If you're just changing the moniker, rather than introducing it for the first time, you probably don't have to worry about this since it is presumably already set. When you assign to the linkedReform attribute, assign it as a text string -- the same way it appears when you show its value from Typescript. Finally, the UpdateLink attribute controls whether the link is Automatically or Manually updated. If you want it to be manual, set this to 0.

See Also


Import and Export dataOLE linkingDatabase access
Comments


You are not allowed to post comments.