UPDATE: see additional info at the end of my original question in response to @Selvin's comment.

I'm trying to implement an RTD server for use with Excel. Test environment:

Excel: Microsoft Office Excel 2007 (32 bit) with all security options to their most relaxed settings (basically allow everything.

My application:

• Built using Visual Studio 2012 (yes, I need to upgrade one of these days)
• WPF .exe with .NET 4.5
• Forced 32 bit build (Task Manager confirms it's loading as 32 bit) to make things simpler.
  It should be possible for 32 bit Excel to connect to a regular 64 bit build of my application since it's an executable and should be running out-of-process, but since it didn't work I'm trying to simplify things. Getting the 64 bit properly registered in WOW6432Node so that it can be found by 32 bit apps is a bit tricky.
• My RTD Server test code is based on Kenny Kerr's excellent series of articles on RTD https://asp-blogs.azurewebsites.net/kennykerr/My-Articles
• Using the 'no Excel reference' approach (hardcoded GUIDs for the interfaces)
• Executable has a strong name
• Executable is codesigned (certificate is from Certum). Tried both the regular SHA256 hash and also SHA1 just in case Excel 2007 doesn't know how to handle that although I would think it simply calls the relevant Win32 API functions to check it.

I've tried registering the RTD server two ways:

• Using RegistrationServices.RegisterAssembly - which seems to work fine (returns true and registry entries are created)
• Also tried using RegAsm which also appears to work fine (no errors or warnings at all), it just says 'All Types registered successfully)

Here's how I test:

• Open Excel

• Enter the following formula in Excel

  =RTD("MyApplicationProgID", "", "test")
  

• After a very short delay Excel displays #N/A

• Excel does appear to have found my executable because if I try to delete the executable I get a message 'The action can't be completed because the file is open in Microsoft Office Excel'. If I close Excel I can delete the executable

• At no point does my executable show up in Task Manager so while Excel keeps a file read lock on it, it doesn't start it.

• I also tried running my application before entering the formula in Excel but that does not work either.

I expected my application to be started and my breakpoints in ServerStart (IRTDUpdateEvent callback) and ConnectData(int topicId,ref Array strings,ref bool newValues), but none of that happens.

Since my application's executable file is locked by Excel but does not actually get started I'm guessing Excel does not like my application for whatever reason but since there is no error message I have no idea what it could be. Any suggestions as to the cause or a way to find out what the actual problem is?

I could try building a simple RTD client and see if that will at least connect. If that doesn't work either it might be easier to figure out why that is not working but I'm hoping someone has any suggestion before I do that because I don't need an RTD client and it might not tell me a lot about what is going wrong with Excel.

End of original question

UPDATE: additional info

First of all, I got it working to a degree. The RTD function as I used it doesn't work, but I found that the following three all do work:

• =RTD("MyApplicationProgID", , "test")
• =RTD("MyApplicationProgID", "local", "test")
• =RTD("MyApplicationProgID", , "test")

Apparently Excel is not clever enough to handle "" as meaning 'local' or 'no server specified'.

My server does still run in-process.

As to @ Selvin's question in the comments ' Why you think that it will be out of process?', that is because afaik RTD was intended to be a replacement for DDE so I assumed (I'll flog myself later) that it would behave in a similar manner. Specifically I thought that an RTD server residing in a DLL would be loaded in-process but an RTD server residing in an EXE would be loaded out-of-process, just like a DDE server.

If you specify a remote server then it will be out-of-process by definition and I had hoped that by specifying a server explicitly it would be run out-of-process as well but alas that is not the case.

Perhaps it would be possible to more or less force Excel to create the server out of process by specifying CLSCTX_LOCAL_SERVER in a call to CoRegisterClassObject but I'm not sure that will work and besides I'm using C# with the ComVisible attribute and RegAsm to register the service so I wouldn't know how to do that anyway. It's been over 20 years since I last did anything COM related so I don't want to go that route.

So it seems that, unlike DDE, RTD does not solve the inter-process communication part of the equation. I could in theory just let Excel create in-process instances of my server but there are two problems with that:

1. The user will be often be running our application alongside Excel and all the calculations (which are fairly expensive) would be performed twice: once by the executable (which can only be run once btw) and once by the RTD server instance.
2. The datafeed that our application connects to only allows a single instance to connect at a time. Rather silly but it's unlikely this will change. This could be worked around but it would be a fair amount of work.

I'm also a bit puzzled by this:

C# Excel RTD Server Multiple Instances disconnect all formulas on one sheet stops formulas on the other

which suggests that person has an out-of-process RTD Server since he writes 'Is there a way in C# to force each Excel instance to have it's own RTD server' but maybe he is misunderstanding what is happening on his system. Supporting multiple Excel instances is definitely a requirement in my case so it looks like I will need an in-process RTD server anyway to cope with that.

I also found this

A C# client for Excel real-time data (RTD) servers code repo: https://github.com/stevtig/Andrew-Sheppard/tree/master/MyRTD

which has this in the comments:

    // Test both in-process (DLL) and out-of-process (EXE)
    // RTD servers.

but looking at the code I think the person who wrote this is mistaken and that his EXE is also still being called in-process. I have downloaded the code and will check what it does.

Basically what we have in our application is multiple tables that contain information on securities (stocks, currencies etc) with various analytical results in the columns. I want this information to be accessible from Excel with real-time updates. The old-fashioned way is to do this through DDE and I thought RTD would be a better alternative.

Even if RTD worked as I expected there's still the downside that in order to register the RTD server you need elevated rights. But at least the user doesn't have to install an Excel Add-In or macro or whatever. I would prefer users not having to install an Excel macro or Add-In.

In any case to make this work I apparently need to devise my own inter-process plumbing to let the in-process RTD server instances connect the the main application (starting it if necessary) to get the data.

I could use WCF or maybe gRPC or deal directly with more low-level mechanisms such as pipes or a local socket connection (which I have used in the past for somewhat similar purposes, just not with Excel). Some sort of proper home-spun web api would also be an option but I need to be able to push the updates to Excel and I'm not sure how that would work with a web request.

Another approach could perhaps be to use an in-process RTD server which in turn connect to the application instance through out-of-process RTD, is that something you can do from an RTD client?

This has gotten a lot more complicated than I anticipated.