VSTO Excel Com Add to Ribbon Not loading

Question

I am using VS 2010 to develop an Excel 2007 COM add-in. Since this is a VS 2010 Office Project, it focuses on the .NET 4.0 client platform. I added a new Ribbon (XML) element called MyRibbon, so the default file names from this file are AdAdInIn.cs, MyRibbon.cs and MyRibbon.xml.

Everything builds perfectly. It is published with the extension .vsto. When I install the add-in (through the provided Setup.exe), it takes Excel as installed and is checked in the list of COM add-ins. It is also designed to be loaded at startup. However, either opening Excel first or opening an Excel file does not add a tab to the ribbon.

I can say that add-ins are loading because it puts the COM add-in in the first cell of the first sheet. It seems that CreateRibbonExtensibilityObject () is not getting the call.

Does anyone have any ideas or can I say how to display error messages that may be buried?

Details below.

I added an override to CreateRibbonExtensibilityObject ():

protected override Office.IRibbonExtensibility CreateRibbonExtensibilityObject() { return new MyRibbon(); } 

MyRibbon.xml looks like this: three buttons in a group inside a tab:

 <?xml version="1.0" encoding="UTF-8"?> <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Ribbon_Load"> <ribbon> <tabs> <tab id="TabAdvComTracking" idMso="TabAdvComTrackingMso" label="Adv.com Tracking"> <group id="groupPrepare" label="Prepare"> <button id="GenerateNewWorkbook" idMso="GenerateNewWorkbookMso" enabled="1" size="large" onAction="GenNewWorkbook" label="Make" /> <separator visible="1"/> <button id="ClearData" idMso="ClearDataMso" enabled="1" size="large" onAction="ClearData" label="Clear" /> </group> <group id="GroupDoIt" idMso="GroupDoItMso" label="Just Do It"> <button id="CaptureIds" idMso="CaptureIdsMso" enabled="1" size="large" onAction="CaptureData" label="Eat" /> </group> </tab> </tabs> </ribbon> </customUI> 

MyRibbon.cs looks like this:

 using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Reflection; using System.Runtime.InteropServices; using System.Text; using Office = Microsoft.Office.Core; using Excel = Microsoft.Office.Interop.Excel; namespace AdvComTrackingAddIn { [ComVisible(true)] public class MyRibbon : Office.IRibbonExtensibility { private Office.IRibbonUI ribbon; public MyRibbon() { } #region IRibbonExtensibility Members public string GetCustomUI(string ribbonID) { //ribbonID when loaded into Excel should be Microsoft.Excel.Workbook return GetResourceText("AdvComTrackingAddIn.MyRibbon.xml"); } #endregion #region Ribbon Callbacks //Create callback methods here. For more information about adding callback methods, select the Ribbon XML item in Solution Explorer and then press F1 public void Ribbon_Load(Office.IRibbonUI ribbonUI) { this.ribbon = ribbonUI; } public void GenNewWorkbook(Office.IRibbonControl control) { Excel.Workbook newWorkBook = Globals.ThisAddIn.Application.Workbooks.Add(); Excel.Worksheet newWorkSheet = (Excel.Worksheet)newWorkBook.Worksheets.Add(); newWorkSheet.Name = "DBTS " + GetDateRange("MMDDYYYY"); } public string GetDateRange(string format){ string day = DateTime.Now.DayOfWeek.ToString(); int offSet = 0; if(day == "Sunday") offSet = 1; else if(day == "Monday") offSet = 0; else if(day == "Tuesday") offSet = -1; else if(day == "Wednesday") offSet = -2; else if(day == "Thursday") offSet = -3; else if(day == "Friday") offSet = -4; else if(day == "Saturday") offSet = -5; DateTime MondayStartDate = DateTime.Now.AddDays(offSet); return MondayStartDate.ToString(format) + "_" + MondayStartDate.AddDays(4).ToString(format); } public void ClearData(Office.IRibbonControl control) { Excel.Sheets wksheets = Globals.ThisAddIn.Application.Worksheets; Excel.Worksheet sheet; for(int i = 0; i < wksheets.Count; i++){ sheet = wksheets[i]; if(sheet.Name.StartsWith("DBTS")){ sheet.get_Range("A6:H12").Clear(); sheet.get_Range("A16:D22").Clear(); sheet.get_Range("A26:D10000").Clear(); } else if(sheet.Name == "Advisory.com Activity"){ sheet.get_Range("A4:B10000").Clear(); sheet.get_Range("D4:F10000").Clear(); sheet.get_Range("H4:J10000").Clear(); } else if(sheet.Name == "Unique Hits Per URL"){ sheet.get_Range("A4:E10000").Clear(); } } } public void CaptureData(Office.IRibbonControl control) { } #endregion #region Helpers private static string GetResourceText(string resourceName) { Assembly asm = Assembly.GetExecutingAssembly(); string[] resourceNames = asm.GetManifestResourceNames(); for (int i = 0; i < resourceNames.Length; ++i) { if (string.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0) { using (StreamReader resourceReader = new StreamReader(asm.GetManifestResourceStream(resourceNames[i]))) { if (resourceReader != null) { return resourceReader.ReadToEnd(); } } } } return null; } #endregion } } 

Finally, ThisAddIn.cs looks like this:

 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; using Microsoft.Office.Tools.Excel; using System.IO; using System.Reflection; namespace AdvComTrackingAddIn { public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { Globals.ThisAddIn.Application.get_Range("A1").Value = "COM add-in loaded"; } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { GC.Collect(); } protected override Office.IRibbonExtensibility CreateRibbonExtensibilityObject() { return new MyRibbon(); } #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 } } 

Answer 1

You must remove the override of the CreateRibbonExtensibilityObject. By default, this is implemented by the base class ThisAddIn and calls CreateRibbonObjects. You can either override CreateRibbonObjects (this should return an array of all your tape objects), or simply let the default implementation of CreateRibbonObjects be implemented (that is, reflect the entire assembly every time your addin starts).

You can learn more about how all of this connects in this post.

Answer 2

I ran into the same problem. I had the wrong path to the XML file and it returned empty:

  public string GetCustomUI(string ribbonID) { return GetResourceText("CheckThisIsTheCorrectNameSpace.Ribbon.xml"); } 

Hard coding the namespace as a string is not a good idea, mainly because they are not refactoring, as well as useful reasons that our colleague Stackoverflow mentions in the comments below.

Answer 3

I have the same problem when trying to change the default TabAddIns tab. I see that idMso is for tabs in the control panel, and id is for new tabs. The following works for me.

 <tab id="TabAdvComTracking" tag="TabAdvComTracking" label="Adv.com Tracking" visible="true" insertAfterMso="TabAddIns">