Conditionally Load Excel Add-ins

I currently have 17 add-ins loaded. Can that be right? Let me count again. Yep, seventeen. Six of the add-ins live in C:\Documents and Settings\Dick.NEBRASKA\Application Data\Microsoft\AddIns, which is the default location for add-ins on my machine. There are a couple that live elsewhere on my local drive. It all boils down to eight add-ins that are on network shares.

Putting add-ins on network shares rocks. I can install the add-ins on client machines around the office and be sure they’ll always be running the latest version. I like the method from a deployment perspective, but there’s a problem. When I’m traveling and start Excel, it complains. To wit,

\NetworkShareMyaddin.xla could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.

Yeah, that makes sense. I’m not connected to the network, so it can’t load the add-in. If I’d put those add-ins on the C drive, no problem. A few of them write to a database on the server, so they wouldn’t do any good locally anyway. If it is was one add-in, I would probably just ignore the error. But with eight add-ins, it takes a long time for Excel to load and it wastes precious airport battery life. I’ve come up with these four alternatives:

  1. Live with the slow load times when not connected.
  2. Move the add-ins to the C drive on all the client machines and code something that checks the version against the server. This way I could let users know when a new version is available.
  3. Code something that loads the add-ins dynamically depending on the availability of the network. It would be like a master add-in that loads all of the other add-ins. This would also simplify my menu problem
  4. Ask the super smart readers of DDoE what they think.

I’ve chosen #4.

18 thoughts on “Conditionally Load Excel Add-ins

  1. I vote for #3. It’s all do-able, if a little tedious to put together. It has the added advantage of getting around the Add-in Manager, which can only be a Good Thing.

  2. Ha! I have the exact same issue. And, I’ve been meaning to do #3, just haven’t had the time.

    Mike, I don’t think it should be that tedious though, should it? It’s just a simple add-in that needs to read/write to a simple database, and load the add-in if the file can be located using DIR.

    In addition to the advantage of getting around the Add-in Manager (very slow), it would also allow you to control load order. That may/may not be important, but I can see where it would have uses if you are trying to use shared ribbon tabs… you’d want the master idQ loaded first, of course.

  3. “Putting add-ins on network shares rocks. I can install the add-ins on client machines around the office and be sure they’ll always be running the latest version.”

    My understanding is that this is not best practice, though I don’t recall exactly where I read this (Either PED or Programming Excel with VBA and .NET).

    #3 is a great idea, but can you combine any of the add-ins to lower the overall number?

  4. In my previous life as an ERP sysadmin we had a few hundred users who all needed Excel addins distributed to their PCs (ie: copy to c: via robocopy on login) and version checked and logged everytime they ran it.

    So this was pretty much option 2 you describe.

    The version checking and logging was done by a function like CHECK_VERSION() and LOG_ACCESS(). They were both similar in that they hit a webserver via a http request to validate the version number plus send who they were and which routine they called. The fast http calls weren’t noticeable since these were pretty hefty routines that did accounting journal uploads, maintenances schedules and occasionally mass terminations of hundreds to thousands of lines.

    The version checking / logging was only done when running the routines, and if the function was out of date they got a custum msgbox() with details on what to do (this was only ever a problem while we were sorting out new sites).

    The version checking gave complete control. The logging was extremely valuable as i could tell what XLA components were used and where we should spend our development efforts.

    I’m not sure if this is close to your situation or not, but i’d be happy to put some code and details together if you’d like to drop me an email.

  5. I’m still using an addin I wrote called FlyBar that loads addins on the fly when the user opens associated workbooks. It also downloads newer addin versions from the server automatically. It does some other cool things, such as running all addin events from a workbook class that triggers off of application events, resulting in code-free workbooks. It’s confusing enough that I can’t explain it well, but I’ve been using it for years with a few users and it makes addin updating, maintenance and even creation pretty pleasant.

  6. Much lower tech, I load Excel via batch file. That is, the various shortcuts that launch Excel call the batch file, and my Excel files are associated with the batch file.

    The batch file checks for the existence of the network login script on the appropriate network share. If it finds the file, then there’s a network connection. If there’s a network connection, I use REG.EXE to load appropriate registry setting before launching Excel. If there’s not a network connection, I use REG.EXE to delete OPEN# values under HKEY_CURRENT_USERSoftwareMicrosoftOffice11.0ExcelOptions that point to any drive other than my C: drive.

    This is a configuration issue, and I guess I’m just very used to using batch files or other forms of scripts to deal with different configurations depending on network or peripheral connections.

  7. I think number 2 is the standard approach, you can’t do 3, what if the addin is needed by the user? That’s also counts out 1. So 2 is the only real solution. Nice of Charles to have already written it for us though ;-). Thanks Charles!

  8. I have lots of “download and install yourself” addins, when latest version is not critical. And I have #3 on a shared folder, when I know that all users of it can access the drive.

    Nice thing then is; you can code it to open read-only, for safety, code is not to be fixed and saved by users at rare runtime errors. And you can set something whatever that prevents it from opening at user machines at times when you want to maintain and fix the addin. The “file is in use” problem is pretty volatile when it comes to Excel addins, and I for one do not want to do maintenance coding at 4am.

  9. Couldn’t you make the folder, where the add ins sit on the server, available offline through windows offline folder option? Then if you’re not connected you still have a local copy of the add in that Excel sees and can use, and when you do connect to the network it will be synced in case there is a newer version on the server.

  10. I use a different approach at work. Instead of add-ins, I create toolbar loaders. In short, there is workbook on a network share (obviously users must have access to it). When a user navigates to that location and opens the workbook they are presented with a user form with checkboxes for them to choose the “macro” they want to install. This then installs a toolbar with a button for each selected “macro”. The catch is that each button references a “macro” in a SEPARATE worksheet that happens to reside in the same location as the toolbar loader. This allows me to protect the workbook, and I am able to change what the code does, yet the users don’t have to update anything. Since the toolbar is always there (unless you go back into the loader and check the box to remove the toolbar) there are never any complaints when Excel starts when you are not on the network. Of course, if you are away and click on one of the toolbar buttons you will get a complaint…. but that’s to be expected.

  11. I do something a little different. I have a workbook on a network share that functions as a toolbar loader. When users open it they are presented with a user form with checkboxes to select which “macro” (not everyone understands VBA) they want to install on their toolbar. The toolbar is installed on their local Excel, but the buttons point individual routines in a SEPARATE workbook (also located where the toolbar loader is). This way I can modify the code to my heart’s content, and it’s all transparent to the users. Excel never complains on a remote start up, because it is not an “add-in” that it is trying to load. Of course, if one of the toolbar buttons is clicked when remote it will complain… but that’s to be expected.

  12. Oops… I thought my first post didn’t take. You can delete one if you like (or both, if you want to make me feel bad).

  13. Certainly option 2. Why hack Excel into doing something that comes with Windows by default?
    I would go with Marc’s suggestion.

    Interesting that you have so many Add-Ins. It must take an age to start up.
    I’ve found Excel becomes an even more useful tool when it’s available immediately (ie. click the Excel icon, and *bang* it’s up). I require zero-add-ins to get level of speed.

  14. I like network add-ins as well. Here are a couple ideas for managing manual add-ins:

    1. I have an alternate Add-In directory on my machine which holds all the local stuff I might need, and I leave a shortcut to it in the AddIns directory. That way, if I’m travelling, I can manually select add-ins from an alternate path without a lot of navigating. Combined with Marc’s approach, you could cover everything.

    2. I don’t add all my add-ins at startup. For instance, Name Manager is activated by a toolbar control that was copied from the Name Manager menu. The resulting button points diretly at the add-in, no code!, and no refernece is made in the add-in list. I just have to make sure that the add-in file name does not change, ’cause then the button fails. Oddly, since I use Name Manager every day, it would be fine to set up as an auto-load add-in, but this approach has worked just fine.

  15. Hi! I am SO GLAD this thread exists… It helped me a LOT with distributing my Excel addins. Charles has a superb piece of work.

    My only question would be – is there something similar for PowerPoint? Or should I try to rewrite Charles’ plugin to ppam.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.