• Deploying Add-ins in a Network Environment

    The purpose of this article is to provide corporate developers with a method to release, maintain and update an add-in in a network environment. This is based upon my strategies for doing this same.

    Assumptions made in this article are:
    • You are a developer of some caliber, understanding enough VBA to create your own routines.
    • You know that your add-in routines should be targeted at the ActiveWorkbook object primarily, but may refer to ThisWorkbook on occasion. (Not the other way around.)
    • You have a network environment to work with.
    • You need to develop your add-in, publicly release it (within your company), and maintain/upgrade it in future.
    Installing an add-in in a Standalone Environment
    Installing an add-in in a standalone environment is easy. You pretty much make sure that all of your code is geared to run on "ActiveWorkbook" instead of "ThisWorkbook", give it a menu structure, if you so desire, and save it as an .xla file. Upon doing so, you immediately reap the following benefits:
    • You've got a code library that you can snap in/out of excel whenever you desire.
    • It's far more portable than the personal.xls file.
    • You can "secure" the project, which pretty much just means that you don't have to see the modules all pop up every time you enter the VBE. (A VBProject password can easily be stripped, so don't think of it as true security.)
    But what if you work in a network environment? What if you want all your users to work from the same add-in and get the benefit of the "single update" method to coding?

    Strategies for installing an add-in in a Network Environment
    This is actually pretty easy, in truth, but there's a couple of "gotchas" that you want to be on the lookout for. The following is the strategy that I use for deploying and maintaining my network add-ins. We're going to go through all these steps in detail, but a summary to begin with.
    • Create the add-in on your local PC
    • Program a routine to update the add-in from your local copy and save it to the network drive
    • Remove any previously badly set up add-ins if necessary
    • Install the add-in for your users
    Create the add-in on your local PC
    It is a best practice to keep your development copy and live version separate. To this end, we'll create our add-in on our local PC, and the "live" version on the network.

    The initial benefits that you can expect to see from this approach are:
    • Some minor performance benefits due to running locally versus across the network.
    • An isolated environment to test and debug your application in, without having to worry about some user opening your file.
    The bigger benefits, however, are realized once you've released your add-in to the general corporate public; You can work on your development copy without affecting the live copy. This allows you to:
    • Track down bugs by trial and error testing, if necessary,
    • Add/modify/delete code,
    • Create/enhance or completely revamp features,
    .. all without having to worry about a user who may stumble into a broken procedure as you are building or testing.

    So the first step in our installation process is therefore to convert our xls file to a local xla file on our development PC. Simply complete the xls file as far as you are able, set up the menu structure if you desire, and save it as an xla file.

    Create a procedure to save the add-in to the network drive
    With many, if not all, programs, the first time a file is opened, it is locked by the user who opened it. Excel is no exception. The first user in gets full command of the file. It cannot be edited or overwritten so long as that user still has control of it. It is therefore extremely important that you save your network version as read only.

    "Now hang on," you may say, "I've shared an add-in without marking it read only!". It's true that you can. Unlike regular workbooks, they give no warning when one or more subsequent users open the file. Excel is designed to read a copy of the add-in into memory for you if someone already has it locked. From a day to day processing perspective, there is no difference to the end user.

    But we've already identified that you are a developer, or you wouldn't be reading this. Chances are then pretty good that you'll want to update your add-in at some point, and Murphy's law will dictate that you'll want to do that when a user has your add-in open. So long as you have not saved the network version of your add-in as read only, the first user that opens it prevents you from saving over the file. If you have multiple users working from that file, you'll need to track down who it is, kick them out, and get that file saved before the next person jumps in.

    True, there are ways to find out which user has the file open. Ivan F Moala has an awesome example on his site of how to find out who has the file open. I used that method for a long time, just phoning the person and kicking them out, but the read-only method is superior as the phone call becomes unnecessary. It also takes a whole lot less code.

    The following routine is a variant of a procedure that I use to save my development add-in file as a new, or over the existing, network add-in. For the purposes of this demonstration, we'll assume that the drive you wish to use for your network add-ins is "F:\Addins". I would suggest that you modify the routine by moving the strAddinDevelopmentPath variable to a module level Private variable which holds the actual path to your addin, but felt that this code would be enough to get you started with the method.

    Sub DeployAddIn()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: To deploy finished/updated add-in to a network
    '               location as a read only file
        Dim strAddinDevelopmentPath As String
        Dim strAddinPublicPath As String
        'Set development and public paths
        strAddinDevelopmentPath = ThisWorkbook.Path & Application.PathSeparator
        strAddinPublicPath  = "F:\Addins" & Application.PathSeparator
        'Turn off alert regarding overwriting existing files
        Application.DisplayAlerts = False
        'Save the add-in
        With ThisWorkbook
            'Save to ensure work is okay in case of a crash
            'Save read only copy to the network (remove read only property
            'save the file and reapply the read only status)
            On Error Resume Next
            SetAttr strAddinPublicPath & .Name, vbNormal
            On Error Goto 0
            .SaveCopyAs Filename:=strAddinPublicPath  & .Name
            SetAttr strAddinPublicPath & .Name, vbReadOnly
        End With
        'Resume alerts
        Application.DisplayAlerts = True
    End Sub
    Providing that this is the first time you have saved this add-in to the network, you can skip the next step. If you've already deployed your add-in once before, though, please read the next step and make sure it is set up correctly.

    Remove badly set up add-ins if necessary
    If you have previously installed the add-in on the network, and copied the add-in to local folders for your users, you will want to remove the prior installation. You should only need to do this once, as setting up in the manner I'm describing will negate the necessity of this step in future. For the purpose of this example, assume your add-in is called "Myaddin.xla", which was installed to the local add-in folder on each client's machine.

    To remove the bad install, on each client machine, do this:

    Remember! Only do this on the client machines, not your development PC!
    • Open Excel and make sure that your old version of the add-in is loaded
    • Open the VBE, and enter the following code in the immediate window:
      ? Workbooks("Myaddin.xla").FullPath
    • Take note of the path
    • Close the VBE, and uninstall the add-in (Tools|add-ins)
    • Close Excel and open Windows Explorer
    • Navigate to the path that you took note of, and delete the "Myaddin.xla" file (you may want to make a backup first)
    • Open Excel
    • Try to install the add-in again (Tools|add-ins --> Check the box beside "Myaddin.xla")
    • You should see the following error:
    • Click okay, close the add-ins window
    • Close Excel - do not attempt to install the new version now!
    • Re-open Excel, and you're ready to install the new version
    It is very important that Excel gets closed between uninstalling and installing a new add-in with the same name. The closure updates the registry keys associated with the add-in and actually removes the old path from the registry. Failure to close will leave you with a path still pointing to the old file (which no longer exists!)

    It seems like a lot of steps, but it's actually quite easy, and again, only needs to be done once for each client.

    Install the add-in for your users
    Now that the add-in has been copied to the network, and now that we know that no hangover installs will get in our way, we can install the add-in for our users. The installation process is similar to installing a local add-in, but with one important difference.
    The steps to install an add-in in a network environment are:
    • Open Excel
    • From the Tools menu, select Add-ins
    • Click Browse and browse to the "F:\Addins" directory
    • Double click your add-in
    • You'll now see the following question:
      • 2000 and higher version:

      • 97 version:
      Regardless of the exact verbiage, the answer is NO. You are not interested in copying it to the local Excel add-in folder or library.
    • Once returned to the add-ins manager, make sure the box beside "Myaddin.xla" is checked and say okay
    Congratulations! You now have an add-in that is running off the read-only network installed version.

    Final note
    In future, you'll only need to update the development copy and run the routine to update your add-in. Because all users are using the read-only copy of the file on the network, you'll never have an issue overwriting it, and they will always get the most recent copy when they restart Excel. Deploying to a new user is as easy as installing a stand alone add-in as well, with the only critical piece to remember being to say no to storing a local copy.


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 8 Comments
    1. TMagnay's Avatar
      TMagnay -

      For years I've had a couple of add-ins that multiple users accessed from a shared network drive and always had hassle getting the timing right so I could update them when no-one else was on the system.
      Setting them to read-only and using the method described above is going to make things a lot easier so thank you very much for having this post here

      Note - the xcelfiles.com domain that is also referenced above no longer exists. However it can be accessed on the "Wayback machine" if anyone wants to get hold of the code that will let you know which user currently has a file open - also a great tool but doesn't work for 2007+ files (I'd post the link but not allowed until I've posted 5 times ... grrr).
    1. Ken Puls's Avatar
      Ken Puls -
      Thanks for letting me know about this, and sorry about not being able to add the link. Only way I can keep the spammers at bay.

      Link to the page through the Wayback Machine is http://web.archive.org/web/200805170...sFileOpen.html (For reference, I don't find that these pages render very well in Internet Explorer. They do work great in Chrome though.)
    1. J4sm1n's Avatar
      J4sm1n -
      Thanks for this article!

      Before reading it, I was planning to send the .xlam files via Dos Command line on the /Addins shared folders on EVERY users desktop ...well, anyway, your method is way more efficient than mine. Keeps sharing solution of everyone's problems!
    1. Ken Puls's Avatar
      Ken Puls -
      Cool stuff, glad you found it useful!
    1. MarkW's Avatar
      MarkW -
      I've been looking for a way to easily roll out add-ins to colleagues on a network and update/tweak them without too much disruption.
      This has worked very well and hasn't (so far) disrupted any custom buttons that people have set up to access add in macros quickly.
      Exactly what I was after. Great work.
    1. ramina2000's Avatar
      ramina2000 -
      Hi. I found your article quite interesting. This is partially relate to my problem which I describe below and hope I could get some directions for.

      I am currently working on a project where we are interested in deploying an application level Excel add-in. We use Win7 ultimate and the add-in is developed in .NET 4.5.1 and for Excel 2010 with Visual Studio 2015.

      To get started I have used Visual Studio Installer to deploy a simple add-in to a shared network folder and it works great. The challenge right now is to find a smart update mechanism for updating add-ins on user machines. Our desire is that on startup excel must check if there is a newer version of the add-in on the network folder. If so the user must be prompted to update now or wait.

      The next step would be to call the msi-file if it is an update and the user answers yes to the question. Could you help me with this or refer to other relevant sites/links.
      I would be very thankful for that.
    1. Ken Puls's Avatar
      Ken Puls -
      Hi Ramina,

      Honeslty, I've never built a solution in VS to make that work. The easiest way would probably be to inject a test in the beginning of the code to check the addin's creation date vs the one you have on the network. If it's older, then prompt the user to update. But that still means that you're going to need to update all the existing addins in the wild with that code...
    1. Mountainkiddo's Avatar
      Mountainkiddo -
      Thank-you very much for being so thorough in this description. I recently ran into the problem described in: Remove badly set up add-ins if necessary.

      Thankfully you covered all the necessary detail to get me out of the conundrum. What was happening is on startup Excel was looking for the xla file in the users local app data folder which is where it resided originally. I moved it to the network and deleted the local copy, causing Excel to get very confused. Even though it found the xla file on the network and ran those scripts, it would throw an error message on startup that it couldn't find the xla file locally. Well no kidding since I had deleted that one.

      I thought it was a registry problem but couldn't get the sequence correct until finding your article! Thank-you for saving me from a bunch more headaches!!