Ufdjrw

I want to connect to a running excel, or launch excel, and at run time add a tab on the root / main ribbon /menubar. When I connect with interop to the excel application object I see a dynamic object menubar. It resolves at runtime to a .com object that I can't parse.

I can iterate into each menu and see menu.name, ID, and menu item. It looks like the ribbon items in my running excel, but i can't add or remove or influence items at run time. The menus, menu, menuitem are all Microsoft private. What am i missing to resolve and add/manipulate/remove my own runtime menu items? I don't want to write and compile static or runtime xml (yet). I see other vendors do this. What assembly or include am i missing? Here is what I have from pure hacking it out.

using System;
using System . Collections . Generic;
using System . Linq;
using System . Text;
using System . Threading . Tasks;
using Microsoft.Office.Core;
using System . Linq . Expressions;
using Microsoft . Office . Interop . Outlook;
using Microsoft . Office . Interop . Excel;
using System .Diagnostics;
using System . Runtime . InteropServices;
using System . Runtime . InteropServices . ComTypes;
using System . Diagnostics . Contracts;
using System . Windows . Controls . Ribbon;
using Microsoft . Office . Tools . Excel;
using Microsoft . Office . Tools . Ribbon;
using System . ComponentModel . Design;
using System . Reflection;
using Microsoft . Office . Interop . Access;

private static void ExcelChops ( )
 
 Process [ ] Running = Process . GetProcessesByName ( "Excel" );
 if ( Running . Count()==0 )
 
 return;
 

 Microsoft . Office . Interop . Excel . Application ExcelApplication = ( Microsoft . Office . Interop . Excel . Application ) Marshal . GetActiveObject ( "Excel.Application" );
 if ( ExcelApplication == null )
 
 return;
 

 string ActiveExcelApplicationCaption = ExcelApplication . Caption;
 Windows ExcelWindows = ExcelApplication . Windows;
 int ExcelWindowCount = ExcelWindows . Count;
 XlWindowState WindowState = ExcelApplication . WindowState;
 Window ExcelWindow = ExcelApplication . Windows [ 1 ];
 String ExcelWindoWindowCaption = ExcelWindow . Caption;

 System . Diagnostics . Debug . WriteLine ( String . Format ( "nExcel Application Caption 0 " , ActiveExcelApplicationCaption ) );
 System . Diagnostics . Debug . WriteLine ( String . Format ( "nExcel Window Caption 0 " , ExcelWindoWindowCaption ) );
 System . Diagnostics . Debug . WriteLine ( String . Format ( "Excel Window Count 0 " , ExcelWindowCount ) );
 System . Diagnostics . Debug . WriteLine ( String . Format ( "Excel Window State 0 " , WindowState ) );
 //Microsoft.Office.Interop.Excel.Panes panes = ExcelWindow . Panes;
 //IteratePanes ( panes );

 Microsoft.Office.Interop.Excel.MenuBar aMB = ExcelApplication . ActiveMenuBar;
 IterateMenus ( aMB , 0 );
 System . Diagnostics . Debug . WriteLine ( String . Format ( "0 1 " , "Completed" , ( ( ( System . Environment . StackTrace ) . Split ( 'n' ) ) [ 2 ] . Trim ( ) ) ) );

 
 private static void IterateMenus ( MenuBar aMB , int v )
 

 string caption = aMB . Caption;
 int ndx = aMB . Index;
 dynamic parent = aMB . Parent;
 Menus menus = aMB . Menus;
 int menusCount = aMB . Menus . Count;

 for ( int i = 1 ; i <= menusCount ; i++ )
 
 Menu a = menus [ i ];
 int b = a . Index;
 string c = a . Caption;
 System . Diagnostics . Debug . WriteLine ( String . Format ( "0 1 " , b , c ) );
 IterateMenus ( a , v + 1 );
 

 

 private static void IterateMenus ( Menu A , int v )
 
 string caption = A . Caption;
 int ndx = A . Index;
 MenuItems items = A . MenuItems;
 int itemsCount = items . Count;

 for ( int i = 1 ; i <= itemsCount ; i++ )
 
 dynamic a = items [ i ];
 Type t = a.GetType ( );

 object o = a as object;
 Type to = o . GetType ( );
 String oo = to . ToString ( );
 var occ = to . Name;
 var ooc = to . TypeHandle;

 System . Diagnostics . Debug . WriteLine ( String . Format ( "menu item 0 of 1 2 3 " , i , itemsCount, occ, caption) );
 
 

The ribbon cannot be programmatically controlled as simply as you would hope.

Prior to the ribbon, Office applications had access to MenuBar (undocumented for Excel) and CommandBar objects. A MenuBar is the classic type of menu (File, Edit, View, Window, Help, etc). A CommandBar is the classic type of toolbar (rows of buttons below the menu). With these objects, you could directly manipulate these legacy UI features.

The ribbon is entirely different. With the ribbon, you are not given the ability to arbitrarily manipulate it. This is intended to protect one add-in from another. In order to work with the ribbon, you must have an Add-In to provide XML describing the ribbon configuration you'd like to apply. There are a few ways to create an Excel add-in:

• Use Visual Studio Tools for Office (VSTO)


• Create a COM library (cannot find a good online resource for this right now)


• 
  Here's an option using Visual Studio Code that I have never tried (not sure if this will work for your use case)

Beyond that, I suggest you google for "excel addin ribbon" for various documentation on how to work with the ribbon from within your addin.

The MenuBar and CommandBar objects and properties are still around for legacy and non-ribbon purposes (e.g. displaying a right-click menu). If you create new Application-level CommandBars in versions of Excel that have a ribbon, the new CommandBars are unceremoniously dumped into a generic tab where all the add-ins' CommandBars end up. Tweaking the built-in CommandBars isn't going to affect the built-in ribbon.