Create Ribbon and Custom Task Panes

[Ahmed AlSahaf]

Hi,

I've created a project to provide real time updating data using Excel-DNA and RxExcel. I created UDFs and everything worked perfectly and cells are updating with real time data. Now I wanted to create Custom Ribbon and Custom Task Panes that will provide a way to write the formulas automatically for the user in the cells using the provided interface in the task panes. The interface also provides the user a way to alter the behavior of the UDFs based on his preferences.

To created Custom Task Panes and Custom Ribbon, I created VSTO project which provided me a WinForms designer and an access to the application object so I can interact with the cells and inject values automatically with UDFs defined in the first project.

The problem now is that these are two independent project, dlls, and Addin. How can I use the Custom Task Panes and the Ribbon I designed using the designer with the original Exce-DNA project that provides the UDFs so I have everything integrated in one single Addin?

Here are my requirements

1. Live data update (UDFs)

2. Rich UI providing the user the ability to populate the cells with the UDFs and to alter their behavior based on settings he configures (The UI will access the application object and populate the cells for the user without typing the formulas manually)

Thanks,

Ahmed

[Govert van Drimmelen]

Hi Ahmed,

Your Excel-DNA can have all the ribbon and Custom Task Pane functionality built in.

But you can't mix Excel-DNA and VSTO (everything in the Microsoft.Office.Tools.* namespace) in a single add-in.

The get hold of the COM Application object inside your Excel-DNA add-in, you add a reference to the interop assemblies (Microsoft.Office.Interop.Excel.dll and Office.xll) and then call ExcelDnaUtil.Application - this returns the Application object for the current Excel process, and is an object of type Microsoft.Office.Interop.Excel.Application. From there you have access to the COM object model as usual.

For the ribbon, you can export the CustomUI XML from the VSTO designer, and then add to your add-in a class derived from ExcelRibbon:

[ComVisible(true)]

public class MyRibbon : ExcelRibbon

{

public override string GetCustomUI(string RibbonID)

{

    string customUIXml =

    @"<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage'>

      <ribbon>

        <tabs>

          <tab id='CustomTab' label='My Tab'>

            <group id='SampleGroup' label='My Sample Group'>

              <button id='MyButton' label='My Second Button' image='M' size='normal' onAction='OnButtonPressed'/>

            </group >

          </tab>

        </tabs>

      </ribbon>

    </customUI>";

    return customUIXml;

}

 

public void OnButtonPressed(IRibbonControl control)

{

MessageBox.Show("Hello from control " + control.Id);

} 

}

From Excel-DNA you are working directly with the Office ribbon xml and COM callbacks, so you don't have the high-level event wrappers (e.g. for the button click event) that the VSTO classes give you. If your ribbon is very complicated, you might want to make some wrappers for yourself, but for a simple ribbon it shouldn't be a problem.

For the Custom Task Pane, you make some UserControl (maybe what you have designed for the VSTO project), and then to display it you call: CustomTaskPaneFactory.CreateCustomTaskPane(...) (in the ExcelDna.Integration.CustomUI namespace)

You might find this discussion and task panes in different Excel version of interest: http://www.codeproject.com/Tips/1063935/Implementing-CTPs-in-Excel-and-with-Exce

One advantage of putting the ribbon and CTP support in your Excel-DNA, is that you don't need admin rights or to install anything.

Please write back if you run into any problems moving your VSTO code into your Excel-DNA add-in.

It's not a trivial task, but the end result should be much simpler to deploy and support over the long term.

-Govert

[Ahmed AlSahaf]

Thanks Govert,

It worked :)