Shared Add In - Update Prompts

[Barkworth]

Hi Guys,

I have an add-in (.xlam) file built from scratch. The add-in contains various macros which are currently in a beta stage and several more will be added in the not-too-distant future

This add-in will be used by anywhere from 10-500 users, the majority of which have less than basic excel skills, and so I needed to achieve the following things:

1. Have the add-in auto install to their local add-in filepath from the shared drive ‘master copy’.
- Achieved by saving the add-in in a shared location and then having a copy of this also in a shared location name ‘GroundAddin_INSTALL.xlam’. When double clicked, the file automatically installs in the local add-in filepath.

2. Have a custom ribbon tab which also automatically appears after being installed, displaying clear buttons for each macro.
- Achieved following this guide —> https://wellsr.com/vba/2019/excel/how-to-make-custom-vba-ribbons-in-excel/…
Although I understand they may be easier solutions using programmes like RibbonX. Unfortunately that won’t work for me since my work doesn’t allow programmes to be downloaded from the internet.

3. Upon opening, the add-in checks itself against the ‘master copy’ to see a more up to date version is available. If so, the version installed in the users drive uninstalls, and the more up to date version is installed.
- Aaaand this is where I’m stumped. I literally have no clue how to do this… I can’t be the only person who’s come across this problem, but can’t find anything online. I’ve read a few forum where they discuss running the add-in directly from the shared drive. Again, no clue how to do this, and also I worry with up 500 users there might be some issues with everyone feeding off the same file. I would much prefer each user has their own version locally.

I know this isn’t the solution, nor the correct syntax. I’ve been trying to crack this for probably 60 hours continuous and I’m wearing thin mentally. But essentially this is how I see it playing out:

Workbook_Open ()
Checks P:/Shared/AddIn/GroupAddin.xlam version is > U:/CitrixProfile/Applications/Microsoft/AddIns/GroupAddin.XLAM version
If true
uninstall AddIn.GroupAddin
delete U:/CitrixProfile/Applications/Microsoft/AddIns/GroupAddin.XLAM
Install P:/Shared/AddIn/GroupAddin.xlam

Thanks in advance, I really appreciate and support, guidance, reference to other forums or the answer.

Sam

 

[severynm]

Preface: I know almost nothing about addins and how they interface with excel. However,

Could you add the version to the file name of the addin and then compare based on a substring of the file paths?

Or, I know you can programmatically access the code of a module and search for a string, so I wonder if you can do something similar with the loaded addin code. I have no idea how the addin object model would be structured in excel to access that however, so that's as much as I can do. I might be interested to hear if you find out anything on that front if you decide to pursue it.

 

[Barkworth]

Preface: I know almost nothing about addins and how they interface with excel. However,

Could you add the version to the file name of the addin and then compare based on a substring of the file paths?

Or, I know you can programmatically access the code of a module and search for a string, so I wonder if you can do something similar with the loaded addin code. I have no idea how the addin object model would be structured in excel to access that however, so that's as much as I can do. I might be interested to hear if you find out anything on that front if you decide to pursue it.

Hey Severynm,

You know, I was thinking of going down the exact same route. It seems to make sense because essentially it’s check if current add in file version is < master group file version.

But then I worry the challenge will come when the macro actually gets executed. If the current add in uninstall’s itself then will that not kill the macro?! Also, I’m not sure you can delete the add-in from the add-in filepath when the add-in is in use… which it will always be since it’s installed.

I’m gonna run some trials for the rest of the week.

Cheers,
Sam

 

[GWteB]

@Barkworth, your assumptions are correct. To be specific:

If the current add in uninstall’s itself then will that not kill the macro?!

It sure would.

Also, I’m not sure you can delete the add-in from the add-in filepath when the add-in is in use

You cannot.

In your situation as described I would go for a second "installer hub" add-in. This way you circumvent the restrictions that you yourself already suspected would exist.

The hub should be responsible for checking for available updates of your "various macros" add-in as well as for actually performing that update. Of course you can also use the hub to install other add-ins (that you already have or may make in the future). Since the hub itself can also be subject to updates, at least one other add-in should be made responsible for this. In the current situation it makes sense to use your "various macros" add-in for this. If the hub has a simple, one-time auto-install feature, that would come in handy.

In addition to working around the issues mentioned, there are a few other advantages to this approach. After all, you can include as many update-specific functions, methods and properties in the hub as possible while you can call them from the other add-in, except at the moment the hub actually has to be "off the air" to be replaced.

While it's usually wise to include the current version in the filename, I wouldn't do that in this case. Each change of file name of an already used / installed add-in adds a new entry to Excel's list of available add-ins. Less experienced users who nevertheless discovered the presence of the Developers tab might get confused by such a list. Since this list can only be cleaned manually or by deleting the correct keys in the Windows OS's registry I would go for using the same file name on each update.