sending commands to an application from Excel? COM?

Question

I wrote a device controller (rs232) and it is being used successfully, however users want to view data and control the device (or perhaps communicate through my program) from Excel. I dismissed DDE as an option and found that RTD (IRtdServer) is probably a good start (though no way to send data back to the "server" from the real time data client).

I found these resources for the RTD part: http://support.microsoft.com/?id=327215 and http://support.microsoft.com/?id=327215

This is a multi-threaded app and I had already added the ability to have multiple listeners on the com port so that I could update multiple clients. I will add the COM interface to the EXE.

But what I need after that is some way of controlling my app/proxying commands to the device through my app from Excel.

What would be the best way to do that?

Perhaps another COM interface and calling it from VBA or something? I am not familiar with using scripting from Excel, so perhaps someone can provice sample code or links that show both the code for a COM object and the accompanying VB(A?) code?

Keep in mind that this is an unmanaged C++ application and it cannot be converted to managed or C# right now. Alternatives using C# are welcome as well, but that is a long-term rewrite.

Thanks

EDIT I have an alternative to adding COM support into the existing EXE. I think it is more flexible to add a two-way communications (cross platform - maybe boost or corba or just straight IP based with my own message protocol)

A COM server (or two) can wrap that communications channel - whatever it is. This doesn;t really affect my question at all - I still would like to know the options for controlling an external EXE from Excel.

EDIT Not having to roll out .NET to customers is also an big plus. many of these devices are on PCs that are pretty old and have perhaps NT or XP on them and I don't relish increasing my setup/install package from 700KB to the ridiculous .NET install size...

Answer 1

Option #1:

Create a small COM server - make sure its interfaces are suitable for scripting with the built Visual Basic engine in Excel. (e.g. use simple types and BSTRS).

Write Excel VB Macros to (1) add your own tool bar to excel and (2) call your COM server.

You can also add buttons and other UI elements to sheets and hook them them up to VB macros.

Option #2:

I realize that you do not want to use C# - but automating office, and talking to COM objects is really, really easy in C# with Visual Studio Tools for Office (VSTO). You shoul really look into this option - If done correctly, it shouldn't mean re-writing any of your existing code. Just use C# and VSTO as a bridge between Excel and your RTD server. As with VB, its straight froward to connect UI elements in Excel to C# and then to your RTD server.

Answer 2

Calling a COM object from VBA is straightforward. This SO Question and my answer give an example of how to create a COM object. Calling exposed methods is as you would expect:

object.ExposedMethod(optional params...);