COM Add-ins
Introduced in Excel 2000.
COM Add-ins must implement the Extensibility.IDTExtensibility2 interface.
If you want an add-in to appear in the COM Add-ins dialogs then it must implement this interface.
Once the C# dll has been built it needs to be added to the registry using the RegAsm tool.
In addition to the normal COM Interop registration the dll must also be registered with Microsoft Office.
This requires adding another registry key under HKEY_CURRENT_USER.
Creating
Open Visual Studio 2022 as Administrator.
New Project, Visual C#, Windows Desktop, Class Library (.NET Framework).
Change the Name to "ExcelCOMAddin".
Change the Location to somewhere on your C drive.
Check the .NET Framework version is correct and press OK.
Rename the Class1.cs file to MyConnect.cs.
Add four references to this project, (Project > Add Reference).
(1) Assemblies, Extensions: "Extensibility"
(2) Assemblies, Extensions: "Microsoft.Office.Interop.Excel" (version 15)
(3) Assemblies, Framework: "System.Windows.Forms"
(4) COM, Type Libraries: "Microsoft Office 16.0 Object Library".
Remove the default class.
Add the following interface to the MyConnect.cs file.
namespace ExcelCOMAddin
{
[System.Runtime.InteropServices.Guid("5FA4884F-AA70-40C0-A4EC-F5390930B152")]
[System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsDual)]
[System.Runtime.InteropServices.ComVisible(true)]
public interface IMyConnect : Extensibility.IDTExtensibility2
{
new void OnConnection(
object Application,
Extensibility.ext_ConnectMode ConnectMode,
object AddInInst,
ref System.Array custom);
new void OnDisconnection(
Extensibility.ext_DisconnectMode RemoveMode,
ref System.Array custom);
new void OnAddInsUpdate(ref System.Array custom);
new void OnStartupComplete(ref System.Array custom);
new void OnBeginShutdown(ref System.Array custom);
string GetCustomUI(string RibbonID);
}
Add the following class underneath the interface in the MyConnect.cs file.
[System.Runtime.InteropServices.ProgId("ExcelCOMAddin.MyConnect")]
[System.Runtime.InteropServices.ComDefaultInterface(typeof(IMyConnect))]
[System.Runtime.InteropServices.Guid("DE3911A1-C82F-42F1-BEFB-F3802E1B6D34")]
[System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)]
[System.Runtime.InteropServices.ComVisible(true)]
public class MyConnect : IMyConnect
{
Microsoft.Office.Interop.Excel.Application _ApplicationObject;
Microsoft.Office.Core.COMAddIn _AddinInstance;
public void OnConnection(
object Application,
Extensibility.ext_ConnectMode ConnectMode,
object AddInInst,
ref System.Array custom)
{
this._ApplicationObject = (Microsoft.Office.Interop.Excel.Application)Application;
if (this._AddinInstance == null)
{
this._AddinInstance = (Microsoft.Office.Core.COMAddIn)AddInInst;
this._AddinInstance.Object = this;
}
System.Windows.Forms.MessageBox.Show("onConnection");
}
public void OnDisconnection(
Extensibility.ext_DisconnectMode RemoveMode,
ref System.Array custom)
{
this._ApplicationObject = null;
this._AddinInstance = null;
}
public void OnAddInsUpdate(ref System.Array custom)
{ }
public void OnStartupComplete(ref System.Array custom)
{ }
public void OnBeginShutdown(ref System.Array custom)
{ }
public string GetCustomUI(string RibbonID)
{
return "";
}
}
}
Make sure the 2 Guid Ids are replaced using (Tools > Create GUID).
Select (Build > Build Solution).
At this point no registry entries are added in the registry.
Select (Project > Properties).
Display the Build tab.
Tick the "Register for COM Interop" checkbox (requires admin rights).
Close the Properties window.
Select (Build > Build Solution).
When the solution is built a "TLB" file appears in the "\bin\Debug\" folder.
When the solution is built the RegAsm tool automatically runs.
The RegAsm tool adds the necessary COM Interop class and interface information to the registry.
C:\Windows\Microsoft.NET\Framework\v4.0.30319\ RegAsm /codebase ExcelCOMAddin.dll
The registry keys that are generated are exactly the same as the COM Interop ones.
Registry Keys - COM Add-in
Microsoft Office COM add-ins are identified by reading from the registry.
You can view the registry by going to the Windows start menu, choosing Run, typing regedit and pressing OK.
There must be a key representing the COM Add-in under the Addins key:
64 Bit Office - HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\
32 Bit Office - HKEY_CURRENT_USER\Software\Wow6432Node\Microsoft\Office\Excel\Addins\
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ExcelCOMAddin.MyConnect
(String)(Default) - (value not set)
(String) Description - A Long Description
(String) FriendlyName - ExcelCOMAddin
(DWord) LoadBehavior - 2
Description - A brief description of the add-in.
FriendlyName - A user friendly name that is displayed in the COM add-ins dialog box.
Load Behavior - The load behaviour of the add-in.
Load the COM Addin
Open Excel, Create a new blank workbook.
Select (File > Options) and select the Add-ins tab.
Change the Manage drop-down to "COM Add-ins" and press Go.
Find "ExcelCOMAddin" in the list.
Tick this entry and press OK.
SS
When the add-in loads the following message box will be displayed.
SS
Close Excel.
Ribbon.xml
Add a new XML file called "Ribbon.xml" to the project.
Select (New > XML File) ??
Add the following XML.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui>
<ribbon>
<tabs>
<tab id="MyNewRibbonTab"
insertBeforeMso="TabInsert"
label="BETTER">
<group id="Group_ID_One" label="MyGroup">
<button id="Button_ID_One"
imageMso="Help"
onAction="Button_One_Click"
label="Button 1"
size="large"/>
<separator id="Separator_ID_One"/>
<button id="Button_ID_One"
imageMso="Help"
onAction="Button_One_Click"
label="Button 1"
size="large"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Change the Build Action property to "Embedded Resource"
SS
GetCustomUI
Add the following code to the MyConnect.cs file
public string GetCustomUI(string RibbonID)
{
return GetResourceText("COMExcelModelCover.Ribbon.xml");
}
private static string GetResourceText(string resourceName)
{
System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
string[] resourceNames = asm.GetManifestResourceNames();
for (int i = 0; i < resourceNames.Length; ++i)
{
if (string.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0)
{
using (System.IO.StreamReader resourceReader = new System.IO.StreamReader(asm.GetManifestResourceStream(resourceNames[i])))
{
if (resourceReader != null)
{
return resourceReader.ReadToEnd();
}
}
}
}
return null;
}
Displaying the Ribbon
Rebuild the Project.
Open Excel.
The Message box will appear again, press OK
The will also be an additional ribbon tab displayed.
SS
Adding the Callbacks
Add the following two method declarations to the IMyConnect interface
public interface IMyConnect : Extensibility.IDTExtensibility2
{
//existing code
void Button_One_Click(Microsoft.Office.Core.IRibbonControl control);
void Button_Two_Click(Microsoft.Office.Core.IRibbonControl control);
}
Add the following two methods to the MyConnect class.
public class MyConnect : IMyConnect
{
//existing code
public void Button_One_Click(Microsoft.Office.Core.IRibbonControl control)
{
System.Windows.Forms.MessageBox.Show("Button_One_Clicked");
}
public void Button_Two_Click(Microsoft.Office.Core.IRibbonControl control)
{
System.Windows.Forms.MessageBox.Show("Button_Two_Clicked");
}
}
LoadBehavior DWORD Values
COM add-ins should be installed on a per user basis so that the add-in user settings will move with the user should they log onto a different machine.
The (Tools > COM Add-ins) dialog box only displays add-ins that are registered under the HKEY_CURRENT_USER registry key.
Any add-ins that are installed on a machine basis are registered under the HKEY_LOCAL_MACHINE key.
COM add-ins registered for a machine are available to all users but do not appear in the (Tools > COM Add-ins) dialog box.
The LoadBehavior DWORD values are the same as those used with VSTO Add-ins.
'support.microsoft.com/en-gb/help/824251/the-com-add-in-that-you-added-to-the-templates-and-add-ins-list-is-not
'support.microsoft.com/en-gb/help/291392/excel-com-add-ins-and-automation-add-ins
The load behaviour describes how the add-in will be loaded either:
"loaded at startup" - Bootload = 2 (load on application startup)
"loaded at next startup only" - ConnectFirstTime = 16
"loaded on demand" - DemandLoad = 8 (loaded only when requested by the user)
"Is loaded" - Loaded = 1
"not loaded" - Disconnect = 0
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext