Disabled Addins

[ErikJan]

I (and others) have occasional problems with an (old) addin we use in Excel (2007). I sometimes seem to have problems and then Excel disables it.

To run our applications, the user has to go to the disabled items and re-enable it then. This is a nuisance. The addin is old and we cannot change it.

Could I come up with VBA code that I run when Excel starts (I fogot, but there is a sheet that's loaded when available automatically; Book.xlsb?) which either displays is addins are disabled and/or automatically allows enabling of (some) disabled items?

[HansV]

I'd worry about this add-in. Excel doesn't disable it without a reason. Have you tried finding out whether an updated version is available?

Disabled items don't appear to be exposed in the Excel/Office object model, so I don't see how you could enable an item using VBA.

[Jan Karel Pieterse]

Hans: I've seen situations where Excel disables addins where the problem wasn't in the add-in. I'm under the impression Excel is being a bit over enthusiastic in doing this.

[HansV]

Thanks for the correction, Jan Karel. Do you know whether it's possible to remove an add-in from the Disabled Items list programmatically?

[ErikJan]

Or re-enable it? :-)

[HansV]

Removing an add-in from the Disabled Items list is the same as re-enabling it...

After that, you'll have to load it again, but that can be done programmatically, by setting its Installed property to True.

[ErikJan]

You're right, I'm sorry

[Jan Karel Pieterse]

I found this page:
http://www.windowsitpro.com/article/tip ... bled-items

which says:
"It turns out that Office applications save the full path to each disabled document under the registry key HKEY_CURRENT_USER\ Software\Microsoft\Office\Product Version\ProductName\Resiliency."

[HansV]

Thanks, Jan Karel. So if you know the full path of the add-in, it should be possible to use code like this for Excel 2007:

Dim strFullName As String
strFullName = "..." ' full path of the disabled add-in
CreateObject("WScript.Shell").RegDelete "HKCU\Software\Microsoft\Office\12.0\Excel\Resiliency\" & strFullName
Application.AddIns("...").Installed = True

I can't test it though.

[Jan Karel Pieterse]

Me neither. Hope someone will sooner or later.

[ErikJan]

working on it :-)

[ErikJan]

OK, tried it. Here's the disabled item as I see it in Excel (2007):

Excel Disabled item.PNG

In the registry it looks like this... the name [3F5D053B] doesn't do anything (yet) to me, the binary content shows the path of the add-in separated by NULLs:

Excel Disable Registry.PNG

If I manually delete the "disabled items" key in the registry and then start Excel, the Add in does work again (I do not need to enable anything). Seems to me that if I process the mysterious key entries under "disabled items", fish out the one that is bothering us (by exploring the add-in name in the "value" part of the key) and then disable that one, I should be OK.

Of course, if I do this in VBA code, maybe all I can do is generate a warning as Excel will have to be re-started to load the add-in (unless my code could run before the Excel add-ins but I don't believe that's what happens). Alternatively, maybe the VBA code could disable & enable the addin after I've done the registry fix, maybe that could work. I can try some stuff out but would like to get your feedback first. Why the NULL separators and how do I cope with that, any idea what the key name means?

[HansV]

The path is stored as a Unicode string (2 bytes per character), in a binary value.

Apparently the article from WindowsITPro was incorrect. Processing binary registry values is a pain, I wouldn't go that way. You could delete the entire DisabledItems key instead, but that's risky of course - there could be more than one disabled item. But if you're willing to experiment, you could try that.

[ErikJan]

Well... I've developed some code and I can already read the keys (still meaningless to me) and the paths in each key. Then I need to add recognition of the sting ".xla", find the add-in name between that sting and the last "\" and then delete that key. From there I'll need to see what happens...

I still need to 'clean up' the code and make it more generic.

[HansV]

Good luck! Let us know the result...

[ErikJan]

OK I have code (will post relevant pieces when all is done).

It reads the registry and searches for a certain addin. When it finds this on listed as disabled, it deletes the registry key. When I then restart Excel, the add-in is indeed no longer disabled.

The question now is: how do I use this code... I'd think it coould maybe run when Excel starts, warn the user and suggest fixing the registry. I'd then like to restart Excel but opening the original file(s)... would that be possible.

Other suggestions are welcome as well of course!

[HansV]

You could first run the code to check whether the add-in is disabled.

If so:

- Remove the registry entry.
- Put up a message box to inform the user.
- Quit Excel.

If not (for example when the user restarts Excel after the add-in has been removed from Disabled Items):

- Loop through the AddIns collection until you find the add-in.
- When you have found it, check whether its Installed property is True.
- If not, set Installed to True.

[ErikJan]

I believe the enabled state is maintained. So when I re-start Excel after the deletion of the registry key all is working directly.

I'd like to be able to re-start Excel opening the same file as the user selected before I closed it... is that possible?

[HansV]

Not so easily. I'd instruct the users to open the workbook again.

[ErikJan]

I think the code is ready, I'll have to test tomorrow and then I'll post here