How can I add rows in real time?

Jul 22, 2012 at 7:02 PM

I have a requirement to show real time market data(OHLC) in excel sheet on real time. Data should be add in new row when price comes.

Can Excel-DNA support this functionality?

Coordinator
Jul 23, 2012 at 10:19 AM
Edited Jul 23, 2012 at 10:19 AM

Yes, I think you could make a macro that does this. It would be a lot easier if you didn't want to add new rows but just display updated data - for that you'd make an RTD server.

Will the Excel be interactively used while you do this? If so, you need to know that if the user is editing a cell, or pressing down his mouse button, you won't be able to put data into Excel. Also, every time you run the macro, the user's undo stack will be erased, which can get annoying.

The biggest problem is if you have some event coming in on a worked or threadpool thread, and now you need to tell Excel to run your update macro.

One way is to install a System.Windows.Forms.Timer which allows you to check for new data periodically.

Alternatively, there is some new asynchronous and cross-thread support in the latest source code check-ins on CodePlex. For this you need the following.

  1. Make a class that implements IExcelAddIn, and in the AutoOpen() you should call ExcelAsyncUtil.Initialize();
  2. From you other thread, call ExcelAsyncUtil.QueueAsMacro to run the work.
  3. In the macro, you can either use the Excel C API (via the XlCall class) or the COM interfaces. For COM you get the root Application obejct by calling ExcelDnaUtil.Application.

-Govert