Have Geek Brain

Get data from custom application in EXcel

I would like to be able to link some data from a custom application to a cell in Excel.

What technique would you use for this? I am, first of all, a Unix developer, and do not know the depth of win32 technologies. But as far as I understand, DDE will be the easiest, although it is a very old technology.

Being able to use it with other office suites like OpenOffice will be a big bonus.

+1


a source to share


8 answers


What does it mean to "link data from a custom application to a cell in Excel"?

Do you want your Excel sheet to display data generated by some external application?

If so, there are several options. Excel Data Provider is one way. This works if you have a lot of data that you want to download. For just one cell, this may be overkill.



If the external data is available through a network interface (say HTTP), you can write some Excel macro code to use it. You can use ServerXmlHttp object to use it from Excel. Some pointers to this are HERE.

Another way to do this is to expose data through a custom COM object, a reusable construct that's very handy for Excel. You have to write some code to do this, but it's not too difficult. You can write this in .NET, C ++, Javascript, VBScript, PerlScript, ...

+2


a source


In .NET, I would use Excel COM Libraries. You can use Workbook, Worksheet and Range objects to refer to specific cells in specific worksheets and workbooks. They work well with .NET (especially VB.NET).



+1


a source


The COM interface is very simple. You start by recording a macro and do what you intend to do. Then translate the generated code into the programming language of your choice.

+1


a source


All of the solutions I've seen here have a data push approach. Why not take responsibility for bringing this data into Excel itself? Excel has the ability to add data via oleDB and ODBC. (at least from the version I know) I can only test this in Office2007, the only version I have on my machines.

All you have to do is add this table to the data tab table. Pretty simple. This way you are also relieved of the burden to maintain the code in your application, to open Excel, to run some code to output the code to cells, etc. Maybe you can record a macro for this and run it when the excel sheet opens.

+1


a source


The easiest way to bind to a cell value is through the .net data providers in Visual Studio 2008. Here is a link to some of the more common methods. Link

It is difficult to try to make it available for office opening since the data providers are not the same.

0


a source


I would recommend using Java / JVM and its Apache POI library.

It can read and write Excel files as well as pure cross-platform Java. This is pretty reliable in practice.

http://poi.apache.org/

0


a source


Yonathan,

If the source application stores data in a database or text file, it will be possible to use the excels function in the data function to retrieve it. Otherwise I would agree with Dayton to integrate with VS2008. You can use VS2005, however you need to install VSTO.

Regarding DDE - in contrast to the fact that you are almost outdated, there are problems when using different versions of Excel and different languages, for which you will need to use different keywords for the same operation.

0


a source


Just to clarify: DDE works with an open office. I'm a Bloomberg user, and I opened an Office 2003 spreadsheet in an open office that contained bloomberg links to stock prices updated via DDE and it works like a charm.

0


a source






More articles:


All Articles