Addins and different Excel versions

[ErikJan]

I have a sheet that uses an add-in. If I check the location of the add-in (from the Excel VBA References dialog), I see something that ends with ...Office12\Library\Addin_name.xlam

I run Office 2010 (and before 2007) and this works. When I copy this Excel file to a new PC that has Office 2013 loaded and I open the Excel file, everything continues to work (but I now see that the reference to the add-in point to ...Office15\Library\Addin_name.xlam)

So far so good; it seems that somehow, Excel automatically and dynamically fixes the add-in location.

My problem is that on a very similar system (actually, theoretically the system should be identical), this does NOT work and my code generates an error as the link to the add-in (@ Office12) is now invalid. The right add-in is indeed present in the Office15 folder but somehow here the automatic and dynamic change does NOT seem to occur...

Anyone with a clue as to what could be causing this here??

[HansV]

If you open a workbook in a newer version of Excel than the version in which the workbook was last saved, references are usually updated automatically.
But it generally doesn't work the other way round: if you open a workbook in an older version of Excel than the version in which it was last saved, this will break most references (except for the default ones to the Excel and VBA object libraries).

[ErikJan]

Right, understood. But I now have a sheet saved in Office 2010 (Win7 32bit) which I open on two basically identical systems in Office 2013 (Win7 64bit). On one references get updated, on the other one they don't...

Is there a setting somewhere maybe? Would it depend on how the add-in is loaded (the add-in itself is in exactly the same place on both Office 2013 systems)

[HansV]

You could check whether the add-in is listed in a different way in the registry on the PCs with Office 2013.

- Start RegEdit.
- Search for addin_name.xlam

You could also check whether the add-in paths are different on the PCs with Office 2013:

- Activate the Visual Basic Editor.
- Press Ctrl+G to activate the Immediate window.
- Type (or copy/paste) the following, then press Enter:

Code: Select all

? Application.LibraryPath

- Note the result.
- Type (or copy/paste) the following, then press Enter:

Code: Select all

? Application.UserLibraryPath

- Note the result too.

Excel looks in both locations for add-ins.

[ErikJan]

The LibraryPath and UserLibraryPath entries are identical. I see several hits in the registry when I search for the add-in, still need to compare these (would be great if I'd had a tool that would search all and save them to a file...)

[HansV]

O&O RegEditor (free) shows all search results in a single list.

[Rudi]

You could look at the freeware app called RegScanner (from Nirsoft.)
It allows you to search for registry entries and can export the table of search results to a .reg file or .html for reference.

[ErikJan]

I searched for the addin in the registry for both systems. In both I see an two entries which are almost identical:

HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\RecentDocs\.xlam
HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\RecentDocs

For the system that works well, I see two more:

HKCU\Software\Microsoft\Office\15.0\Excel\Options (followed by OPEN1 and REG_SZ and then the add-in with path)
HKCU\Software\Microsoft\Office\15.0\Excel\AddInLoadTimes (followed by the add-in with path then REG_BINARY and a hex string)

I do NOT see the latter two in the system that fails. Still, if I load only Excel on the failing system and look at the loaded add-ins, I see the add-in in the correct folder (Office15).

[HansV]

The entries for RecentDocs can be ignored - they just point to recently opened files.

I think the HKCU\Software\Microsoft\Office\15.0\Excel\Options\OPEN entry is the relevant one: it tells Excel to open the add-in each time it starts. you could try adding this entry on the other computer.

One more place to look: HKCU\Software\Microsoft\Office\15.0\Excel\Add-in Manager.

More info: Installing An Excel Add-In Using Setup Factory.

[ErikJan]

OK, so it looks like I need to add the OPEN-key to the registry (it's a mystery why that key is there on my system and not on the other system).

Can I enable this key to be added by a manual action from Excel?

If now, I can create a REG file and have that executed, however, if people already have an OPEN1 (and OPEN2, and...) with something else there, that will do more harm than good... any ideas how I can get this implemented?

[HansV]

Jan Karel's article provides some code for that purpose. Although it is not in VBA, it shouldn't be too hard to adapt it for use in VBA. You'd need to add routines for reading and writing registry entries; those can easily be found.

[rory]

The OPEN key should be automatically generated when you check the add-in in the add-ins dialog. Otherwise it shouldn't be loading when Excel does.

[ErikJan]

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Add-in Manager does not exist on the failing system, still if I try to add the add-in by browsing Excel telss me that its already loaded... (but it doesn't work if I start a sheet that requires it...).
Also, the bad system doesn't have an OPEN entry with the addin mentioned... the good one has (here HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options).
I'll try to create a tool that adds the next OPEN entry there but this remains mysterious to me (and more systems have this behavior)

[ErikJan]

Update:

Found this information (which at least explains why I can't see anything in the "Add-in Manager" entry sometimes):

The Add-in Manager key indicates if an add-in is currently loaded but not ticked/installed.
This is updated when Excel closes.
Any add-ins that have been added under the "Options" key and then manually unticked by the user are removed from the "Options" key and added here