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$ Commented Jun 10, 2013 at 2:54
  • $\begingroup$ @rm-rf It's funny, because I can do it in Excel, but not in Mathematica? $\endgroup$ Commented 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$ Commented Jun 10, 2013 at 2:57
  • $\begingroup$ @rm-rf wow.. it works! $\endgroup$ Commented 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$ Commented 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$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.