MSOfficeInterop - Xortron 5000 Ultimax SuperBeast

I have searched high and low for a clear example of a managed C# way to create excel and office add-ins. I had some key requirements in my search:

The solution should work
The solution should be simple
The add-in should support excel UDFs
The add-in should support custom toolbars and GUIs
It should be written in managed C#

There are a host of different ways to create excel add-ins. They include XLAs, XLLs, Shared Add-ins, COM add-ins Automation Add-ins, VSTO, and all different sorts of combinations that mix and match and use shims and other things that were never entitrely clear to me. I've seen a lot of discussion on the differences between these solutions and what they can or can't do but I haven't seen very many working samples. So, instead of a lengthy discussion on my selection I'm going write up a working example of my solution.

So, first an overview of what I've done for my solution. I could not find one clear solution that gave me toolbar and UDF access that didn't require complex shims or other shenanigery, so I've created a mix of two solutions. First I created a shared add-in. Second, I created an automation add-in for my UDFs and added this to the same project. I've created a reference to this in my shared add-in. Finally, I've reference the automation add-in in the shared add-in so that I can consolidate code.

The main two steps are well documented. There is a shared add-in template in visual studio so it is easy to create these and there is a well written step by step tutorial on Microsoft support. There are a variety of samples out there for creating an automation add-in, the two easiest to fine are the MSDN one and the Code Project one. Both of these are informative, but neither one worked for me. They give some helpful conceptual information but like I said, I couldn't get them to work. Not to worry, I found another tutorial a few months ago that I can't find again so I can't give credit, but I do have the working code and I'll show it to you. I'll go through the entire process step by step here since I, like you, am tired of jumping from place to place looking at partial solutions.

With that, Let's begin!

The Shared Add-In

As I said before, I got this portion from this Microsoft site.

From Microsoft Visual Studio go to File->"New Project"
In the new project dialog go to "Other Project Types"->Extensibility and select Shared Add-in
I certainly hope you are capable of naming a project all on your own.
In the wizard select the following options as you go through

Create an Add-in using Visual C#
Select whichever office products you're interested in. If you're feeling nervious, start with excel. If you're feeling bold, select them all. I dare you.
Give it a name and description.
I selected both options on the fourth page.
Finish it.

Add a reference to System.Windows.Forms
Add using statements for System.Windows.Forms and System.Reflection
Add a button to the connect class:
private CommandBarButton MyButton;
Implement the members of IDTExtensibility2 in your Connect class as follows:
/// <summary>
///      Implements the OnConnection method of the IDTExtensibility2 interface.
///      Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
///      Root object of the host application.
/// </param>
/// <param term='connectMode'>
///      Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
///      Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
{
applicationObject = application;
addInInstance = addInInst;

            if (connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup)
            {
                OnStartupComplete(ref custom);
            }
}

/// <summary>
///     Implements the OnDisconnection method of the IDTExtensibility2 interface.
///     Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
///      Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
///      Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection(Extensibility.ext_DisconnectMode disconnectMode, ref System.Array custom)
{
            if (disconnectMode != Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
            {
                OnBeginShutdown(ref custom);
            }
            applicationObject = null;
}

/// <summary>
///      Implements the OnAddInsUpdate method of the IDTExtensibility2 interface.
///      Receives notification that the collection of Add-ins has changed.
/// </summary>
/// <param term='custom'>
///      Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate(ref System.Array custom)
{
}

/// <summary>
///      Implements the OnStartupComplete method of the IDTExtensibility2 interface.
///      Receives notification that the host application has completed loading.
/// </summary>
/// <param term='custom'>
///      Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete(ref System.Array custom)
{
            CommandBars oCommandBars;
            CommandBar oStandardBar;

            try
            {
                oCommandBars = (CommandBars)applicationObject.GetType().InvokeMember("CommandBars", BindingFlags.GetProperty, null, applicationObject, null);
            }
            catch(Exception)
            {
                //Outlook has the commandBars collection on the Explorer object.
                object oActiveExplorer;
                oActiveExplorer = applicationObject.GetType().InvokeMember("ActiveExplorer", BindingFlags.GetProperty, null, applicationObject, null);
                oCommandBars = (CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars", BindingFlags.GetProperty, null, oActiveExplorer, null);
            }

            //Set up a custom button on the "Standard" command bar
            try
            {
                oStandardBar = oCommandBars["Standard"];
            }
            catch (Exception)
            {
                //Access names its main toolbar Database.
                oStandardBar = oCommandBars["Database"];
            }

            //In case the button was not deleted use the existing one.
            try
            {
                MyButton = (CommandBarButton)oStandardBar.Controls["My Custom Button"];
            }
            catch (Exception)
            {
                MyButton = (CommandBarButton)oStandardBar.Controls.Add(1);
                MyButton.Caption = "My Custom Button";
                MyButton.Style = MsoButtonStyle.msoButtonCaption;
            }

            MyButton.Tag = "My Custom Button";

            MyButton.OnAction = "!<OfficeAddin.Connect>";

            MyButton.Visible = true;
            MyButton.Click += new _CommandBarButtonEvents_ClickEventHandler(MyButton_Click);

            oStandardBar = null;
            oCommandBars = null;
}

/// <summary>
///      Implements the OnBeginShutdown method of the IDTExtensibility2 interface.
///      Receives notification that the host application is being unloaded.
/// </summary>
/// <param term='custom'>
///      Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnBeginShutdown(ref System.Array custom)
{
            MyButton.Delete();
            MyButton = null;
}

        void MyButton_Click(CommandBarButton Ctrl, ref bool CancelDefault)
        {
            Functions.HelloWorld();
        }

	Search this site