# Excel DDE Link: reading and writing in real time

I'm wondering if it is possible to link Excel and Mathematica in real time, so that Mathematica can be used as a computing and "storage" tool. Suppose I have an Excel spreadsheet with an active DDE link: I would like to know if it is possible for Mathematica to communicate with Excel in real time and store every cell update in Excel. I know there is a package called ExcelLink, with several functions to link Mathematica and Excel. So, if I use, for instance ExcelRead["A2"] I get the correct value

19.36

However, it is not dynamic! I can get dynamic values by using

a=Dynamic[Refresh[ExcelRead["A2"], UpdateInterval -> 0]]
b=Dynamic[Refresh[ExcelRead["B2"], UpdateInterval -> 0]]


However, when I try to add a+b I get

19.36+28.56

And, worse, If I use

Dynamic[a+b, UpdateInterval -> 0]


I get

19.36+28.56

So, here arises my first problem: although Mathematica reads the values in real time from Excel, I'm not able to perform any calculation with them. Is there any way to do it?

My second problem is: how can I write dynamically the values I'm getting from the DDE Link inside Excel itself? I need to write every updated value in Excel in a sequence like shown below • Umm... you can't add two Dynamic objects! – rm -rf Jun 10 '13 at 2:54
• @rm-rf It's funny, because I can do it in Excel, but not in Mathematica? – Rod Jun 10 '13 at 2:55
• I don't know what you mean by doing it in Excel (I don't have Excel), but the a and b you have in Mathematica have the head Dynamic, for which the operation + is not defined. What does Dynamic[Setting@a + Setting@b, ...] give? – rm -rf Jun 10 '13 at 2:57
• @rm-rf wow.. it works! – Rod Jun 10 '13 at 2:58
• Well, I've just never needed Excel in my life and I use LaTeX for all my Word/Powerpoint needs, so I've not needed the entire Office suite either. Why do you use UpdateInterval -> 0? Can't you do it slower? What's the point in updating like crazy if you don't need to? You'll quickly run out of memory at this rate... – rm -rf Jun 10 '13 at 3:10

## 1 Answer

After some research I've found this partial solution:

Step 1: Open Excel (if you have it!)

Step 2: Open Excel Link

Needs["ExcelLink"]


Step 3: If you don't have any DDE Link for Excel, simulate real time data using

Dynamic[Refresh[Excel["B1"] = RandomInteger, UpdateInterval -> 1]]


Step 4: Import back to Mathematica your simulated data

a = Dynamic[Refresh[ExcelRead["B1"], UpdateInterval -> 1]]


Step 5: Create a numberic list and set Dynamic[] to append sequential numbers to it every 1 second

numlist = {}; k = 1;
num = Dynamic[Refresh[AppendTo[numlist, k++];Last@numlist, UpdateInterval -> 1], TrackedSymbols -> {}]


Step 6: Create a string list (for cell values) and set Dynamic[] to append sequential values to it every 1 second

cellslist = {}; j = 1;
cell = Dynamic[Refresh[AppendTo[cellslist, "A" <> ToString[Last[numlist]]];Last@cellslist, UpdateInterval -> 1], TrackedSymbols -> {}]


Step 7: Store the values in Excel itself!!! (thanks to @rm-rf for the Setting tip!)

Dynamic[Refresh[Excel[Last@cellslist] = Setting@a, UpdateInterval -> 1]]


Result # IMPORTANT

This is a solution using UpdateInterval->1` . However, I would appreciate any solution with asynchronous reading and writing in real time.