Share data between VSTO and Excel DNA App domains
Is there a way to share data between a VSTO add in and an Excel DNA add in? Or can the Excel DNA add in be loaded into the VSTO's app domain?
The Excel-DNA add-in will always be in a separate AppDomain. You might try to pass an object via the AddIn's Object property. Any MarshalByRef object works across AppDomains, but you need make the initial link...
Finally I ended up with this approach:
1) Put the sharable objects in a class lib and mark them COM visible:
namespace SharedLib { using System.Runtime.InteropServices; [ComVisible(true)] [InterfaceType(ComInterfaceType.InterfaceIsDual)] public interface ISharedObject { int GetVSTOInstanceNumber(); } [ComVisible(true)] [ClassInterface(ClassInterfaceType.None)] public class SharedObject : ISharedObject { private readonly int seed; private int serial; public SharedObject(int seed) { this.seed = seed; } public int GetVSTOInstanceNumber() { return ++serial + this.seed; } } }
2) In VSTO create an instance of the shareable object and expose it via RequestComAddInAutomationService:
namespace SampleExcelAddIn { using SharedLib; public partial class ThisAddIn { private const int Seed = 10; private ISharedObject sharedObject; protected override object RequestComAddInAutomationService() { if (sharedObject == null) { sharedObject = new SharedObject(Seed); } return sharedObject; } ... code removed for brevity ... } }
3) Now consume the shared object in the Excel DNA class lib:
namespace ExcelFunctions { using ExcelDna.Integration; using Excel = Microsoft.Office.Interop.Excel; using SharedLib; public class ExcelFunctions { [ExcelFunction(Description = "Gets a serial number from the VSTO")] public static int GetVSTOInstanceNumber() { var application = (Excel.Application)ExcelDnaUtil.Application; object addinName = "SampleExcelAddIn"; var addin = application.COMAddIns.Item(ref addinName); var utils = (ISharedObject)addin.Object; return utils.GetVSTOInstanceNumber(); } } }