2

I have a working RtdServer-based automation add-in:
How do I create a real-time Excel automation add-in in C# using RtdServer?.

Creating a VBA wrapper is trivial:

Function RtdWrapper(start)
    RtdWrapper = Excel.Application.WorksheetFunction.RTD("StackOverflow.RtdServer.ProgId", "", start)
End Function

This works. I have attempted to create a C# wrapper as follows:

[ClassInterface(ClassInterfaceType.AutoDual)]
public class RtdWrappers
{
    private readonly Microsoft.Office.Interop.Excel.Application _application = new Application();

    public object Countdown(object startingCount)
    {
        var start = Convert.ToInt32(startingCount.ToString());
        return _application.WorksheetFunction.RTD("StackOverflow.RtdServer.ProgId", string.Empty, start);
    }

    [ComRegisterFunctionAttribute]
    public static void RegisterFunction(Type t)
    {
        Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
    }

    [ComUnregisterFunctionAttribute]
    public static void UnregisterFunction(Type t)
    {
        Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
    }
}

When I enter "=Countdown(150)" into a cell in Excel it shows the initial value of 150 which is returned by ConnectData but never updates. Is there some callback that I should register? Am I instantiating the Application object correctly? What am I missing?

Thanks,

Frank

Community
  • 1
  • 1
Frank
  • 3,029
  • 5
  • 34
  • 43

1 Answers1

2

Indeed, you are not getting hold of the right Application object. One solution is to implement the IDTExtensibility2 interface in your add-in. This interface has an OnConnection method that Excel will call when loading your add-in. In this method you are passed the Application object which you can keep in a local variable for later use.

Govert
  • 16,387
  • 4
  • 60
  • 70
  • I have not checked it either - and subsequently I have run into an article (http://blogs.officezealot.com/whitechapel/archive/2005/04/10/4514.asp) which suggests IDTExtensibility2 is not used on an automation add-in. If so, looks like using somethiong like Excel-DNA for your Excel add-ins might be the only way to do this in .NET. – Govert Apr 20 '11 at 13:46
  • But in this Stackoverflow answer http://stackoverflow.com/questions/1998824/help-with-excel-automation-addin, Mike quite emphatically states that you can implement IDTExtensibility2 on an automation add-in to get hold of the Application object. – Govert Apr 20 '11 at 22:03
  • Yes, COM Add-ins and Automation Add-ins are both simply classes exposing COM interfaces. There is no reason that they cannot both be within the same assembly. Just have the COM Addin class save the Excel.Application reference in a static field, then this static field can be referenced from the automation add-in class residing in the same assembly. – Mike Rosenblum Jul 31 '12 at 04:22
  • One unclear issue is whether the IDTExtensibility2 interface on that COM class is called when the library is only loaded as an Automation Add-In to provide UDF functions. AFAIK the IDTExtensibility2 interface is probed for and called when the UDF server is activated, even of it is not registered as a "COM Add-In" with Excel. But I haven't checked recently. – Govert Jul 31 '12 at 13:03