Delphi Excel COM-Addin

Question

I am writting an MS Excel COM-Addin in Delphi 2006. I'm have Excel 2007 installed on my development machine.

I started the project as an ActiveX Library and then added an Automation object from the Delphi ActiveX project menu.

In my automation object I defined the IDTExtensibility2 interface as

IDTExtensibility2 = interface(IDispatch)
  ['{32E456FC-C710-43AA-9ACA-DDE8F132B91B}']
  function OnAddinsUpdate(var w_Custom: OleVariant): HResult; stdcall;
  function OnBeginShutDown(var w_Custom: OleVariant): HResult; stdcall;
  function OnConnection(const w_Application: IDispatch; w_ConnectMode: Integer;
                      const w_AddInInst: IDispatch; var w_Custom: OleVariant): HResult; stdcall;
  function OnDisconnection(w_DisconnectMode: Integer; var w_Custom: OleVariant): HResult; stdcall;
  function OnStartupComplete(var w_Custom: OleVariant): HResult; stdcall;
end;

and implemented the interface in a class that derived from TAutoObject.

In the initialization section of the unit I call

TAutoObjectFactory.Create(ComServer, TPBSExcelAddin, Class_PBSExcelAddin, ciSingleInstance, tmApartment);

com object registers fine and shows up in Excel Addin options however it will in install in excel. I just get the error "Not loaded. An error has occurred during the loading of the COM Addin"

Can anyone see a problem with my interface? or the way I am creating the com object? is there a way to debug this?

Thank you

I tried to do this some while back. It's hard to get it right. Eventually I discovered Add-in Express which I cannot recommend highly enough. — David Heffernan
Thanks David. I have considered Add-in Express but I would rather do it from scratch for my own learning. — There is no spoon

David Heffernan David Heffernan · Accepted Answer · 2011-09-06T11:47:51

You've got your declaration of IDTExtensibility2 wrong. It should be:

IDTExtensibility2 = interface(IDispatch)
  ['{B65AD801-ABAF-11D0-BB8B-00A0C90F2744}']
  procedure OnConnection(const Application: IDispatch; ConnectMode: ext_ConnectMode;
                         const AddInInst: IDispatch; var custom: PSafeArray); safecall;
  procedure OnDisconnection(RemoveMode: ext_DisconnectMode; var custom: PSafeArray); safecall;
  procedure OnAddInsUpdate(var custom: PSafeArray); safecall;
  procedure OnStartupComplete(var custom: PSafeArray); safecall;
  procedure OnBeginShutdown(var custom: PSafeArray); safecall;
end;

One thing that is crucial to remember is that methods in an interface must be declared in the correct order—you declared them in the wrong order. I also don't know where you got your GUID from.

I obtained this interface declaration by importing the type library for Microsoft Add-In Designer. I strongly recommend that you do the same.

Delphi Excel COM-Addin

2 Answers