'Normal' links in Excel are worksheet cell to worksheet cell, or name to name etc. These are covered here.
This page covers the other type of link, the not so common one.
Dynamic Data Exchange (DDE)
This is a pretty old technology these days, well over 10 years old. In general Microsoft now seem to recommend developers choose a different technology for exchanging data live between 2 disparate applications.
Regardless of advice to the contrary there are many applications that make their data available to Excel via DDE. Most of the market data providers (Reuters, Bloomberg, Platts etc) provide DDE access to their data.
If you ever see an unusual formula something like this:
=cododde|ddesource!randnumber
(notice the pipe character | ) If you see this. then the chances are you have DDE links to another application.
The basic formula structure is:
Program name | topic ! Field
If you would like to try out DDE links, we have written a DDE server that makes available data you can consume in a spreadsheet cell.
Its a very simple VB6 app:
It provides 2 pieces of data (or fields in DDE parlance) the time and a random number. If you double click the formula you can paste them directly into a cell and see them update as you play with the form controls.
You can use Edt>>Links>>Update to refresh the worksheet cell values if they get out of sync with the app. This seems to happen more when the workbook has lots of links.
If you close down the exe and try to update the links you get this warning dialog:
This 'Remote data not accessible' warning is the equivalent of the workbook not found error when trying to update normal links. If you click yes then Excel will try to start the exe via DDE. This will only work if Windows knows where to find the app, and it gives up fairly easily with this:
To make sure Windows can find the app (which should run fine from anywhere, on Windows XP), you need to add its location to the 'path'. this is the list of folders Windows looks through when searching for executable files (exe and dll).
You can add paths the the PATH by right clicking 'My Computer'>>Properties>>Advanced>>Environment Variables, then click path, then edit then add your new one on the end with a semi colon ; to separate it from the previous.
DDE link formulas in general keep their last known value, even when the source data application is not running. They do sometimes briefly go to #N/A whilst waiting for a response, especially in Excel 2007 and 2010.
To know if you do have DDE links you can look in the Type column of the Edit>>Links dialog for anything that isn't 'Worksheet'. To find where they are used you can do a find on the pipe '|', or use the Codematic Link Manager Tool (commercial version).
Download The Codematic DDE Server here.
Codematic Link Manager
Rich control and management of external links has been an important feature of so many of our consulting assignments we developed a utility to help. That utility has now been refined into a commercial grade Excel add-in, more information is here.
|
