Calling a running C# application from VBA

Question

I have some VBA code that needs to talk to a running c# application. For what it's worth, the c# application runs as a service, and exposes an interface via .net remoting.

I posted a question regarding a specific problem I'm having already (http://stackoverflow.com/questions/2556163/from-vb6-to-net-via-com-and-remoting-what-a-mess) but I think I may have my structure all wrong...

So I'm taking a step back - what's the best way to go about doing this? One thing that's worth taking into account is that I want to call into the running application - not just call a precompiled DLL...

Answer 1

In the past, one way I accomplished something similar was with Microsoft Message Queueing. Both languages/platforms can read/write to a queue.

In my scenario, we had a legacy Access database that we had to maintain. We wanted to migrate away from it and replace it with a more robust .NET solution. To get real time data out of the current system into the new system, we added VBA code to write data to a message queue. Then we wrote a C# windows service to process that data in the new system.

I'm not entirely sure of what you're doing, so this may not be a fit, but I thought I'd mention it.

Answer 2

I've come up with a solution using my original structure...

That is, the VBA application calls a COM wrapper application that translates all of the types from .Net to COM safe types. This wrapper then calls the main service using .net remoting.

The problem I was having was that the common dlls between the wrapper and the service needed to be in the C:\Program Files\Microsoft Office\Office12 folder (along side msaccess.exe).

While I was using the AssemblyResolve method to provide the dlls at runtime, this wasn't working...So for now I'll just have to have the dlls copied to the folder - a far from elegant solution, but at least the communication is working for now.