6

I would like to be able to allow a user to enter a custom formula in a cell in Excel which will subscribe to data provided by an external .NET application. For example, the user might enter...

=getCurrentValue("ABC")

There is an external .NET (C#) application that is determining the value of "ABC" every minute.

The cell should display the initial value of "ABC" from the external application and update when the external application sends a new value.

In the past I think the external application would be a DDE server and the formula would be a DDE client formula but DDE is apparently dead. What is the "correct" technology to use to do this?

Note that the spreadsheet needs to pull the data via a formula. The information is not going into a fixed template excel file but into any spreadsheet into which the user enters a formula.

Thanks,

John

The Muffin Man
  • 19,585
  • 30
  • 119
  • 191
John C
  • 238
  • 2
  • 8
  • FWIW, I thought the question was very clearly asked, and found the extraordinary high level of irrelevance present in the non-accepted answers to be completely mind blowing. – BrainSlugs83 Mar 03 '14 at 21:57

2 Answers2

5

Excel RTD (Real Time Data) is the replacement for DDE. It allows you to push values to a cell whenever updates are available. If the process providing the updates is an external application, setting up a WCF channel between the RTD server and the external app should allow you to feed real-time updates into Excel.

Here are some links:

bradmo
  • 784
  • 6
  • 7
0

You can communicate with Excel sheets through the Excel Api:

new Microsoft.Office.Interop.Excel.Application();

Hans Leautaud
  • 1,742
  • 1
  • 19
  • 34
  • You could consider CSV files though. Most hostingproviders won't allow Office on there server so you could write to and CSV file. Although you can do much much more with Microsoft.Office.Interop.Excel, you can even create print area's and charts – Hans Leautaud Mar 16 '12 at 20:56
  • 1
    The excel interop appears to be targeted at automating excel tasks or pushing data into excel. It can trigger a DDE request in excel for example. I have adjusted the question to reflect we want excel to pull the data (though updates will be pushed back once excel subscribes). DDE would be perfect but it appears to be no longer recommended. Is there a mechanism to register a formula/callback/something via interop? If so could you point me to an example? – John C Mar 16 '12 at 21:15
  • Something like this? oRng = oSheet.get_Range("C2", "C6"); oRng.Formula = "=A2 & \" \" & B2"; – Hans Leautaud Mar 16 '12 at 21:18
  • 1
    The goal is to allow the user to enter in any arbitrary sheet a formula like "=ShowMostRecentExternalValue("IBM")". The cell would then display a value specific to "IBM" from the external application and the value would update as triggered by the external application (like a stock quote...could change 5 times in a second and then stay the same for a minute). In your example (as I understand it) the external application would have to know how to get the sheet, "C2", "C6", and some parameters to know which data to put in the cells. This was built into DDE. Looking for the equivalent. – John C Mar 16 '12 at 21:36