COM Add-in for Excel doesn't load when Excel is launched by opening file

Several users have reported that if they launch Excel by double-clicking an Excel file, the add-in will not load. But, if they open Excel via the Start menu (or Quick launch toolbar) the add-in loads fine.

Some details, in case they help:

• It is a COM add-in, written in VB6.
• The problem has bee开发者_开发知识库n reported on Windows XP/Excel 2003 and Vista/Excel 2007 systems.
• The add-in implements IDTExtensibility2.
• The start mode is set to "Load on Startup".

Any thoughts on the cause or how to troubleshoot this would be greatly appreciated.

Update: I believe I have found a solution to this problem.

When an IDTExtensibility2 dll is registered, it automatically creates HKCU entries for the load behavior, add-in name, etc. But I also had my setup file register the add-in to HKLM, so that it would be available to all users on a machine. This caused double registry entries on the system.

I didn't think this would be the cause of the problem. I manually edited the HKCU entries and Excel seemed to ignore them and follow the HKLM entries. However, I received a tip from another developer explaining that they had the same problem, and their solution was to delete the duplicate registry entries. I tried it and it seems to have resolved the problem for the (very small number of) people who reported the bug.

The Inno Setup code below will add the HKLM entries, double-check that the load behavior is correct (because I'm paranoid), then delete the HKCU entry. Substitite your file attributes wherever you see ALL CAPS.

[Registry]
Root: HKLM; Subkey: Software\Microsoft\Office\Excel\Addins\CONNECT_CLASS; Flags: uninsdeletekey
Root: HKLM; Subkey: Software\Microsoft\Office\Excel\Addins\CONNECT_CLASS; ValueType: string; ValueName: FriendlyName; ValueData: ADDIN_NAME
Root: HKLM; Subkey: Software\Microsoft\Office\Excel\Addins\CONNECT_CLASS; ValueType: string; ValueName: Description; ValueData: ADDIN_DESC
Root: HKLM; Subkey: Software\Microsoft\Office\Excel\Addins\CONNECT_CLASS; ValueType: dword; ValueName: LoadBehavior; ValueData: 3
Root: HKLM; Subkey: Software\Microsoft\Office\Excel\Addins\CONNECT_CLASS; ValueType: dword; ValueName: CommandLineSafe; ValueData: 0


// Set load behavior to on start up
procedure ResetAddinRegKeys();
var
  bUpdate : Boolean;
  LoadBehaviorKey : Cardinal;

begin
  if RegQueryDWordValue(HKEY_LOCAL_MACHINE, 'SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS', 'LoadBehavior', LoadBehaviorKey) then begin
    if LoadBehaviorKey <> 3 then begin
      bUpdate := True;
    end;
  end else begin
    bUpdate := True;
  end;

  if bUpdate = True then begin
    RegWriteDWordValue(HKEY_LOCAL_MACHINE, 'SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS','LoadBehavior', 3);
  end;

  if RegKeyExists(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS') then begin
    if RegDeleteKeyIncludingSubkeys(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS') then begin;
      //MsgBox('Duplicate keys deleted', mbInformation, MB_OK);
    end;
  end;
end;

function GetCustomSetupExitCode: Integer;
begin
  ResetAddinRegKeys;
  Result := 0;
end;

For my MSI installer, I have the commit section of the installation call the following VBScript:

Sub RemoveAddinHKCUKeys()
    On Error Resume Next 
    Dim WshShell
    Set WshShell = WScript.CreateObject("WScript.Shell")

    WshShell.RegDelete "HKCU\SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS\CommandLineSafe"
    WshShell.RegDelete "HKCU\SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS\Description"
    WshShell.RegDelete "HKCU\SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS\FriendlyName"
    WshShell.RegDelete "HKCU\SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS\LoadBehavior"
    WshShell.RegDelete "HKCU\SOFTWARE\Microsoft\Office\Excel\Addins\CONNECT_CLASS\"

    If Err.Number <> 0 The Err.Clear
End Sub

It's been a long time so my memory is foggy, but I do recall problems with getting COM add-ins to start if the host (Excel, Word) is started as an embedded object. That is, you have a Word document with an Excel document embedded in it (you actually see the Excel cells there in Word). When you double-click the embedded Excel file to work with it, Excel is started, but Excel does not load its COM Add-ins. Then when you start Excel in any other way, you're really just using the Excel that was/is already running from the embedded object and it won't have your COM add-ins.

That doesn't appear to be your problem but I thought you might like some sympathy. ;)

Are you using the Add-in Designer in VB6? I haven't had problems with it, but you try scrapping it and implementing IDTExtensibility2 directly in a class and then writing your own registry entries to register it as a COM add-in. Or do this the otherway around of you're not using the designer.

One thing to try is to register the add-in as a machine-wide add-in, rather than just a user add-in. With the designer you can only register as a user add-in. (Though there is a work around for that).

Are you able reproduce it? Are any of the IDTExtensibility2 methods getting called at all?

I suppose it's possible other add-ins might be interfering. You can download my COM Add-In utility to look at what add-ins are loaded (the COM Add-ins windows in the Office apps only show you the user add-ins, not the machine add-ins.)

http://www.amosfivesix.com/download/stackoverflow/

If the add-in completely stops loading, the Office app may have disabled it. Go to Help | About | Disabled Items and see if it's there.

Excel has some goofy options related to DDE (which is what Explorer uses to open documents in other apps usually.) Tools | Options | General | Ignore other applications. See if that makes a difference.

If you can't reproduce the problem, but your client can, you could write a special version for them that logs the IDT... events to see if they are happening. Send them a macro that checks Excel.Application.Addins to see if you add-in is there (I know Word has that object model, not sure about Excel so forgive me if it does not).

Very easy in 5 steps

1. Open Excel
2. Go to File>Options>Add-Ins
3. Go to Manage: then select Disabled Items then Click Go
4. In new windows Find your add-ins and click Enable
5. Restart Excel