5
$\begingroup$

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:

enter image description here

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

enter image description here

Improve this question
$\endgroup$
17
  • 1
    $\begingroup$ Umm... you can't add two Dynamic objects! $\endgroup$
    – rm -rf ♦
    Jun 10, 2013 at 2:54
  • $\begingroup$ @rm-rf It's funny, because I can do it in Excel, but not in Mathematica? $\endgroup$
    – Rod
    Jun 10, 2013 at 2:55
  • 2
    $\begingroup$ 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? $\endgroup$
    – rm -rf ♦
    Jun 10, 2013 at 2:57
  • $\begingroup$ @rm-rf wow.. it works! $\endgroup$
    – Rod
    Jun 10, 2013 at 2:58
  • 2
    $\begingroup$ 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... $\endgroup$
    – rm -rf ♦
    Jun 10, 2013 at 3:10

1 Answer 1

Reset to default
2
$\begingroup$

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[100], 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

enter image description here

IMPORTANT

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

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.