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

Question

I have created an RTD server for Excel in C# that constantly updates cells with data and needs to work with multiple instances of Excel. The issue is if I have the same RTD formula in more than one instance of Excel and I delete the formula in one of the Excel instances, it calls the DisconnectData method in the RTD server so the identical formulas in the other instances of Excel stop updating even though they should still be updating.

Is there a way in C# to force each Excel instance to have it's own RTD server or is there a way for the RTD server to properly recognize multiple instances of Excel and check that all instances of a formula have been deleted from all excel workbooks before calling the DisconnectDatamethod in my RTD server.

Answer 1

Assuming that your RTD functions are wrapped with a UDF, one solution would be to assign each worksheet an identifier (a Guid, for example) from within the UDF call. You can persist the identifier as a custom worksheet property and then add it to each outgoing RTD topic, which will result in a unique set of topics for each worksheet when they arrive at your RTD server.

Accessing a custom worksheet property with each RTD call will have a performance impact when processing large numbers of functions, however, so you should cache the identifier for a short period of time to mitigate this. One way to do this is to maintain a dictionary lookup keyed by worksheet object. Within the scope of each UDF call, get the worksheet that the calling cell is associated with via the Application.Caller property (cast it to a Range and get the worksheet property from it), and then lookup the identifier in the dictionary.