Distributing Excel 2010 VBA Add-in

[robertgama]

Does anyone know if it's possible to distribute an Excel add-in in such a way that it can be easily updated without having to resend out the file to the users?

I was considering packaging up some functions and distributing it, but I realized that ensuring everyone is on the latest version would probably be a concern.

I must have done something wrong since I searched the forum for Add-in and got 0 results.

Thanks,

Rob.

 

[minimaster]

Robert,

I modified the addin loader from Decision models to provide a means for automatic updates of addins.
the modified version can be downloaded from here: Download AddinLoaderV2.zip

First you need to modify the content of the addinload.txt file to contain the local path to the addin file and as well the path to the network directory where the updates are being placed.
then you can install the AddinloaderV2 as a new addin.
After starting Excel you can access the settings for updates in the "About" menu.

 

[robertgama]

Thank you.

That's funny, I think I may have come across a blog post Daily Dose of Excel » Blog Archive » Conditionally Load Excel Add-ins commented on by Charles Williams last night that linked to this page (Daily Dose of Excel » Blog Archive » Conditionally Load Excel Add-ins). That may have been that same file that you've changed?

I'm just reading the notes and had a question. Is it possible to use a web-location like a public Dropbox folder for the add-ins? If not is this an easy update that can be made as it might be a very useful feature as it would eliminate the need to be connected to a server and allow people to use this even when they are remote as long as they have an internet connection. Another question is what happens when the user is totally offline, does it store the add-in locally and then update it with the version on the Network?

Thanks again, and I'll start looking through the code soon. Preliminarily this looks like it could be a great solution! I noticed he originally posted this in 2009 is everything still able to work in 2010 version?

Have a great day,

Rob.

 

[minimaster]

Robert,

yes the original version comes from Charles. Web locations will need testing and possibly some code modifications. In the file addinload.txt you should specify two paths: 1st the one to the addin file somewhere on the local drive. Thats what will be loaded by default. 2nd a path to a folder where potentially an update can be found. If the addin loader sees a newer file in the remote directory it will ask the user whether he likes to update his local version. Optionally you can specify that the update process happens silently in the background without a Question-box. This option can be found in the mentioned "About" menu entry belonging to the AddinloaderV2- Addin.

 

[robertgama]

Great, that's for the response.

I'm just wondering how the local path is set when a user installs the add-in. Do they select an Add-in to install through the loader for the first time and does it automatically create a local directory on their computers?

Installing Add-in Loader on End User computers

Logically, the Add-in Loader XLA has to be installed once on each End User PC.

1. Go to ToolsàAdd-insàBrowse to the server directory.
2. Choose the Add-in Loader XLA file à Choose OK.
3. Answer NO when asked if you want to copy the Add-in Loader XLA to the Library.

After installed, a new toolbar will be available to the user. From this toolbar, all of the Add-in Loader functionality can be accessed. The “Load” button allows the user to chose which add-ins to load based on what has been set up by the administrator. The “View/Unload” button provides a pop-up form that allows the user to view and/or uninstall the add-ins that have been loaded, whether automatically or by the user, with Add-in Loader.

So in the following example:
MacroLister, auto, C:\_Excel\AddIns\, (\\bhicelclu01\gruehar$\Public\Excel\AddIns\)
is the "C:\_Excel\AddIns\" path set during the installation or do I have to install my add-in for the users and ensure they all have the same directory?


Thanks again,

Rob.

Robert,

yes the original version comes from Charles. Web locations will need testing and possibly some code modifications. In the file addinload.txt you should specify two paths: 1st the one to the addin file somewhere on the local drive. Thats what will be loaded by default. 2nd a path to a folder where potentially an update can be found. If the addin loader sees a newer file in the remote directory it will ask the user whether he likes to update his local version. Optionally you can specify that the update process happens silently in the background without a Question-box. This option can be found in the mentioned "About" menu entry belonging to the AddinloaderV2- Addin.

 

[minimaster]

Robert,

On my PC and the other people ones that use my addins, only AddinLoaderV2 needs to be installed in the normal way an addin is being installed via the Addin manager dialog builtin Excel. All other addins can be loaded then by the addin loader in case they are specified as "auto". The rest specified in Addinload.txt can be loaded on demand via the drop menu that AddinloaderV2 provides under the "Addin" menu - this is a bit different to the original version - basically adjusted to better fit into Excel 2007/2010. You can freely specify the local path and the remote path. None of these paths is automatically created. You have to specify and create these manually. In paralell of course you can have further addins that are installed via Excel dialog and not being handled by the Addinloader.

 

[robertgama]

Okay, so is the new process for installing as follows:

1. Create a local directory for the users' add-in that is the same for everyone [Ex. C:\_Excel\AddIns\]
2. Put your add-in on a server directory [Ex. \\javsbs01\Tech Files\Software\Microsoft\Office\Shared Excel Add-ins]
3. Edit the text file to add the information for at least one add-in [Ex. GamaExcelAddin, auto, C:\_Excel\AddIns\, (\\javsbs01\Tech Files\Software\Microsoft\Office\Shared Excel Add-ins\GamaExcelAddin)]
4. Put my add-in in the proper server directory [Ex. \\javsbs01\Tech Files\Software\Microsoft\Office\Shared Excel Add-ins\GamaExcelAddin]
5. Go to the client machine and load your add-in from the server and do not copy it locally
6. It prompts them to notify them that there is not a local copy of the add-in and asks them if they would like to update from the server

Is that correct?

Thanks,

Rob.

 

[robertgama]

Looks like it's going to work pretty good.

One enhancement request might be to make all of the functions private somehow so they don't appear in the 'user defined' group of functions for users when they are picking a formula in Excel.

Also it looks like any user would be able to edit the 'addinload.txt' file from the toolbar in Excel. Wouldn't that potentially break the add-in for all users if they changed a path or something?

Very slick solution though, thanks for sharing.

Thanks,

Rob.

 

[minimaster]

Robert,
regarding ".... and do not copy it locally"
the primary location for all addins, including AddinloaderV2, should be on the local HD, otherwise the loading will fail when you are offline from the network. I'm sorry I did not explain it very well.

 

[robertgama]

No problem. I appreciate your help.

So to update the add-in itself would that have to be done locally?

Cheers,

Rob.