none
Excel shared Addin and vsto based third party addin RRS feed

  • Question

  • Hello,

    we have a shared Addin written in C#

    [implements  Extensibility.IDTExtensibility2, Office.IRibbonExtensibility]

    now a third Party addin should also be used.

    It Looks like this addin is vsto based, since the contructor of the main class has the form

    Graphomate(	Microsoft.Office.Tools.Excel.ApplicationFactory Factory, 
    		Microsoft.Office.Interop.Excel.Application Application, 
    		Microsoft.Office.Tools.AddInBase AddonBase)
    
    

    I have a Microsoft.Office.Interop.Excel.Application object, but how I get the corrsponding

    Microsoft.Office.Tools.Excel.ApplicationFactory and Microsoft.Office.Tools.AddInBase objects ?

    tia

      Hendrik Schmieder

    Thursday, February 25, 2016 10:57 AM

Answers

  • Hi h_schmieder,

    Based on my test, it can’t cast the type, but it can call the method directly. I tested in a console application, you may try it in a console application and check the result, if you could reproduce that issue, please share the same projects on the OneDrive.

    This is my code:

     [ComVisible(true)]
        public interface IAddInUtilities
        {
            void ImportData();
    
            void ImportDateTwo(Excel.Workbook wd);
        }
    
        [ComVisible(true)]
        [ClassInterface(ClassInterfaceType.None)]
        public class AddInUtilities : StandardOleMarshalObject,IAddInUtilities
        {
            // This method tries to write a string to cell A1 in the active worksheet.
            public void ImportData()
            {
                Excel.Worksheet activeWorksheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
    
                if (activeWorksheet != null)
                {
                    Excel.Range range1 = activeWorksheet.get_Range("A1", System.Type.Missing);
                    range1.Value2 = "This is my data2";
                }
            }
            public void ImportDateTwo(Excel.Workbook wd)
            {
                Excel.Worksheet activeWorksheet = wd.ActiveSheet as Excel.Worksheet;
    
                if (activeWorksheet != null)
                {
                    Excel.Range range1 = activeWorksheet.get_Range("A2", System.Type.Missing);
                    range1.Value2 = "This is my data 3";
                }
            }
        }

      private AddInUtilities utilities;
    
            protected override object RequestComAddInAutomationService()
            {
                if (utilities == null)
                    utilities = new AddInUtilities();
    
                return utilities;
            }

    xcel.Application excel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
                //Excel.Application excel = new Excel.Application();
                //excel.Visible = true;
    
    
    
                Console.WriteLine("press a key to invoke the AddinUtilities object");
    
                Console.ReadLine();
    
                
    
                object addinName = "ExcelAddIn1";
    
                dynamic addin = excel.COMAddIns.Item(ref addinName);
    
                //ExcelAddIn1.IAddInUtilities utils =
                //    (ExcelAddIn1.AddInUtilities)addin.Object;
    
                var utils =addin.Object;
    
                utils.ImportData();
    
                utils.ImportDateTwo(excel.ActiveWorkbook);
    
                Console.WriteLine("press a key to close Excel");
    
                Console.ReadLine();

    On the other hand, if you want the add-in to be installed in HLLM (Local machine), please use windows installer to deploy/install it.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, March 2, 2016 3:12 AM
    Moderator
  • Sorry for the delay.

    Using the Attribut "Object" for the Office.ComAddin does the trick.

    so instead of

    dynamic vstowrapper = null;
    object index = "JedoxVSTOWrapper";
    vstowrapper = _oExcelApp.COMAddIns.Item(ref index);
    

    I use

    dynamic vstowrapper = null;
    object index = "JedoxVSTOWrapper";
    vstowrapper = _oExcelApp.COMAddIns.Item(ref index).Object;
    

    Then this works

    vstowrap.VoidTest();
    vstowrapper wrap = vstowrap.WrapTest();
    vstowrapper name = vstowrap.myUpper(_oExcelApp.ActiveWorkbook.Name);
    vstowrapper name2 = vstowrap.WorkbookName(_oExcelApp.ActiveWorkbook);

    with

    [ComVisible(true)]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface IVSTOWrapperUtilities
    {
    	void VoidTest();
    	string WrapTest();
    	string myUpper(string name);
    	string WorkbookName(Excel.Workbook wb);
    }
    
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public class VSTOWrapperUtilities : StandardOleMarshalObject, IVSTOWrapperUtilities
    {
    	public void VoidTest()
    	{
    		return;
    	}
    
    	public string WrapTest()
    	{
    		return "WraP2";
    	}
    
    	public string myUpper(string name)
    	{
    		return name.ToUpper();
    	}
    
    	public string WorkbookName(Excel.Workbook wb)
    	{
    	return wb != null ? wb.Name : "";
    	}
    }

    • Marked as answer by h_schmieder Thursday, March 3, 2016 1:49 PM
    Thursday, March 3, 2016 1:49 PM

All replies

  • Hi Hendrik,

    Do you want to get ApplicationFactory and AddinBase objects in the excel add-in?

    We could get them from Globals object (e.g. Globals.Factory, Globals.ThisAddIn).

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 26, 2016 5:52 AM
    Moderator
  • A shared Addin has no Globals object !

    regards

      Hendrik Schmieder

    Friday, February 26, 2016 8:40 AM
  • Hi Hendrik,

    I think you want to call VSTO add-in from a shared add-in by using Graphomate method.

    As far as I know, we can’t get the object in VSTO runtime through the shared add-in, for your code, the ApplicationFactory and AddonBase are the objects in VSTO runtime (Microsoft.Office.Tools.XXX), they are initialized in VSTO add-in.

    # Assemblies in the Visual Studio Tools for Office Runtime

    https://msdn.microsoft.com/en-us/library/ee712616.aspx

    I suggest that you could refer to this article to calling code in VSTO add-ins from other office solutions (e.g. com add-in/Shared add-in)

    # Calling Code in VSTO Add-ins from Other Office Solutions

    https://msdn.microsoft.com/en-us/library/bb608621.aspx

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 29, 2016 2:19 AM
    Moderator
  • Unfortunatly the last link doesn't help since it speaks about accesing a vsto solution from another vsto solution.

    This doen't cover our use case.

    We have a Graphomate.dll.

    We put this as reference to our C# Project.

    (Graphomate.dll itself isn't registered as Excel Addin)

    The main class of this dll is called  Graphomate with the conctructor

    Graphomate(	Microsoft.Office.Tools.Excel.ApplicationFactory Factory, 
    		Microsoft.Office.Interop.Excel.Application Application, 
    		Microsoft.Office.Tools.AddInBase AddonBase)
    

    To use this assembly we have to add

    Microsoft.Office.Tools
    Microsoft.Office.Tools.Common
    Microsoft.Office.Tools.Common.v4.0.Utilities
    Microsoft.Office.Tools.Excel

    as references to our C# Project anyway.

    So I hoped that we can get ApplicationFactory / AddInBase by calling some static methods of the Tools classes or

    the System.Runtime.InteropServices.Marshal class

    tia

      Hendrik

    Monday, February 29, 2016 11:13 AM
  • Hi Hendrik,

    As far as I know, for this scenario, we can’t do it. We need to export object in VSTO add-in.

    There is a blog that has the simple sample that can help you.

    # Why your COMAddIn.Object should derive from StandardOleMarshalObject

    http://blogs.msdn.com/b/andreww/archive/2008/08/11/why-your-comaddin-object-should-derive-from-standardolemarshalobject.aspx

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 1, 2016 2:25 AM
    Moderator
  • Ok,

    I ttried to use a second Addin with the second addin beiing a vsto addin and then use in my shared addin

    Office.COMAddIns list

    second Addin:

    ThisAddin.cs:

    namespace VSTOWrapper
    {
        public partial class ThisAddIn
        {
            private Graphomate gmInstance = null;
    
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                gmInstance = new Graphomate(Globals.Factory, Globals.ThisAddIn.Application, Globals.ThisAddIn);
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
                if ((gmInstance != null) && Marshal.IsComObject(gmInstance))
                {
                    Marshal.FinalReleaseComObject(gmInstance);
                    gmInstance = null;
               }
            }
    
            #region VSTO generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }
            
            #endregion
    
    
            private VSTOWrapperUtilities addinUtilities;
    
            protected override object RequestComAddInAutomationService()
            {
                if (addinUtilities == null)
                {
                    addinUtilities = new VSTOWrapperUtilities();
                }
                return addinUtilities;
            }
    
    
        }
    }
    

    VSTOWrapperUtilities.cs:


    namespace VSTOWrapper
    {
        [ComVisible(true)]
        [InterfaceType(ComInterfaceType.InterfaceIsDual)]
        public interface IVSTOWrapperUtilities
        {
            string WorkbookName(Excel.Workbook wb);
        }
    
        [ComVisible(true)]
        [ClassInterface(ClassInterfaceType.None)]
        public class VSTOWrapperUtilities : StandardOleMarshalObject, IVSTOWrapperUtilities
        {
    
            public string WorkbookName(Excel.Workbook wb)
            {
                return wb != null ? wb.Name : "";
            }
        }
    
    }
    

    Now in my shared Addin I use

    dynamic vstowrapper = null;
    object index = "JedoxVSTOWrapper";
    vstowrapper = _oExcelApp.COMAddIns.Item(ref index);
    
    string name = vstowrap.GetType().InvokeMember(
    	"WorkbookName",
    	System.Reflection.BindingFlags.InvokeMethod |
    	System.Reflection.BindingFlags.Public |
            System.Reflection.BindingFlags.Instance,
            null, vstowrap, 
    	new object[]{_oExcelApp.ActiveWorkbook}, 
    	new CultureInfo("en-US"));
    
    string name2 = vstowrap.WorkbookName(_oExcelApp.ActiveWorkbook);
    
    

    But there are two Problems

    1) neither of the two method calls to WorkbookName works. I get always unknown Name.

        Casting to VSTOWrapper.VSTOWrapperUtilities doesn't work either [Unable to cast]

    2) The VSTOWrapper Addin is only registred under HKCU and not under HLLM like the shared Addin.

      Hendrik

    Tuesday, March 1, 2016 2:13 PM
  • Especially there's no entry under

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\Excel\Addins

    Tuesday, March 1, 2016 2:21 PM
  • Hi h_schmieder,

    Based on my test, it can’t cast the type, but it can call the method directly. I tested in a console application, you may try it in a console application and check the result, if you could reproduce that issue, please share the same projects on the OneDrive.

    This is my code:

     [ComVisible(true)]
        public interface IAddInUtilities
        {
            void ImportData();
    
            void ImportDateTwo(Excel.Workbook wd);
        }
    
        [ComVisible(true)]
        [ClassInterface(ClassInterfaceType.None)]
        public class AddInUtilities : StandardOleMarshalObject,IAddInUtilities
        {
            // This method tries to write a string to cell A1 in the active worksheet.
            public void ImportData()
            {
                Excel.Worksheet activeWorksheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
    
                if (activeWorksheet != null)
                {
                    Excel.Range range1 = activeWorksheet.get_Range("A1", System.Type.Missing);
                    range1.Value2 = "This is my data2";
                }
            }
            public void ImportDateTwo(Excel.Workbook wd)
            {
                Excel.Worksheet activeWorksheet = wd.ActiveSheet as Excel.Worksheet;
    
                if (activeWorksheet != null)
                {
                    Excel.Range range1 = activeWorksheet.get_Range("A2", System.Type.Missing);
                    range1.Value2 = "This is my data 3";
                }
            }
        }

      private AddInUtilities utilities;
    
            protected override object RequestComAddInAutomationService()
            {
                if (utilities == null)
                    utilities = new AddInUtilities();
    
                return utilities;
            }

    xcel.Application excel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
                //Excel.Application excel = new Excel.Application();
                //excel.Visible = true;
    
    
    
                Console.WriteLine("press a key to invoke the AddinUtilities object");
    
                Console.ReadLine();
    
                
    
                object addinName = "ExcelAddIn1";
    
                dynamic addin = excel.COMAddIns.Item(ref addinName);
    
                //ExcelAddIn1.IAddInUtilities utils =
                //    (ExcelAddIn1.AddInUtilities)addin.Object;
    
                var utils =addin.Object;
    
                utils.ImportData();
    
                utils.ImportDateTwo(excel.ActiveWorkbook);
    
                Console.WriteLine("press a key to close Excel");
    
                Console.ReadLine();

    On the other hand, if you want the add-in to be installed in HLLM (Local machine), please use windows installer to deploy/install it.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, March 2, 2016 3:12 AM
    Moderator
  • Sorry for the delay.

    Using the Attribut "Object" for the Office.ComAddin does the trick.

    so instead of

    dynamic vstowrapper = null;
    object index = "JedoxVSTOWrapper";
    vstowrapper = _oExcelApp.COMAddIns.Item(ref index);
    

    I use

    dynamic vstowrapper = null;
    object index = "JedoxVSTOWrapper";
    vstowrapper = _oExcelApp.COMAddIns.Item(ref index).Object;
    

    Then this works

    vstowrap.VoidTest();
    vstowrapper wrap = vstowrap.WrapTest();
    vstowrapper name = vstowrap.myUpper(_oExcelApp.ActiveWorkbook.Name);
    vstowrapper name2 = vstowrap.WorkbookName(_oExcelApp.ActiveWorkbook);

    with

    [ComVisible(true)]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface IVSTOWrapperUtilities
    {
    	void VoidTest();
    	string WrapTest();
    	string myUpper(string name);
    	string WorkbookName(Excel.Workbook wb);
    }
    
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public class VSTOWrapperUtilities : StandardOleMarshalObject, IVSTOWrapperUtilities
    {
    	public void VoidTest()
    	{
    		return;
    	}
    
    	public string WrapTest()
    	{
    		return "WraP2";
    	}
    
    	public string myUpper(string name)
    	{
    		return name.ToUpper();
    	}
    
    	public string WorkbookName(Excel.Workbook wb)
    	{
    	return wb != null ? wb.Name : "";
    	}
    }

    • Marked as answer by h_schmieder Thursday, March 3, 2016 1:49 PM
    Thursday, March 3, 2016 1:49 PM