Help needed on project-Automatic background update Add-In to new version in network folder

I have an Add-In I created that is used by several users on a network. I upload new versions from time to time and place in a common folder. I want the users to be constantly using the latest version of the Add-In (to avoid users using an outdated version and reporting errors that is fixed in a current version).

Most of the users do not remember/care about updating to the latest version on a regular basis. So I am looking at altering the Add-In so that it will:

1. check if the shared network folder path can be found at Workbook_Open (in case anything happens to the network folder path)
2. determine if there is a new version in this folder (the versions are determined by a numeric string on the end of the file name. e.g. FILENAME_build20130217.XLAM)
3. copy the new version to the users Add-In folder (I prefer the Add-Ins to be loaded from the users own folders rather than the network path)
4. load the new Add-In from the users Add-In folder

This is what I have so far in ThisWorkbook:

Please Login or Register  to view this content.

Further notes:
CheckFileNameContainsHigherVersionNumber = I am thinking that it may be possible to make use of code from AddLoaderV2 (see attached zip). In the Module 'AddLoadModule' there is a sub 'AddLoader2'. Part of this code examines a folder for files and it will select a single candidate which it decides has the highest version file name. (I am unsure if I have the skill to work this into my current code. )

One potential annoyance I want to avoid is this: When the currently loaded Add-In checks the shared network folder, I don't want it to copy & install the XLAM if it is already the same version as the currently loaded Add-In. (Possibly by checking if ThisWorkbook.Name = the selected candidate file from the CheckFileNameContainsHigherVersionNumber?)

If someone could please kindly solve the 'CheckFileNameContainsHigherVersionNumber' procedure (by integrating the 'determine the latest version XLAM name' from the AddLoaderV2 code - see zip attached to opening post - also I have the main code as a separate module in my current XLSM) then I think I could muddle out the rest.

See attached XLSM file:
In the first module, I have my code for the background updater.
The first sub checks if the network folder is found. If found, then return the latest version of the XLAM from that folder. Then copy the XLAM to the users AddIns folder and auto install into Excel add-ins.
The second module contains the main code from AddLoaderV2. Most of this code in that module is unnecessary for my purposes. I only want to return a string value - being the file name with the highest version number in the network folder.

This may be easier to explain what I expect it to do:
I have attached a dummy (empty) XLAM with the same file name as the XLSM but the version number increased by 1.

1. Download both files to the same windows folder. (Recommend a folder with no other excel files in it)
2. Open the v1 XLSM.
3. When running the macro, it should check the ThisWorkbook folder and return the v2 XLAM file (as this has a higher version number than the current workbook).

But it's not doing it.

why do you want the add-in local? just makes life harder ;-) http://www.excelguru.ca/content.php?...rk-Environment

if you really must then this article will probably help a lot-it's based on internet updating (because on a network you'd do it the easy way of course) but the principles are the same
http://www.jkp-ads.com/Articles/UpdateAnAddin.asp

Originally Posted by JosephP

why do you want the add-in local? just makes life harder ;-) http://www.excelguru.ca/content.php?...rk-Environment

Thanks for the links JosephP. But I don't want to install the add-in to the end users local machines!

I probably haven't explained how the add-in will be used in real life. So here's the background.

I design the add-in as a XLSM on my laptop. When I'm ready to release a new update, I save a copy as a XLAM file and upload the XLAM to a Windows Server folder that is accessible by all users. I don't want the end users linking directly to the XLAM in the shared folder (for the very reasons pointed out in the first website link you provided). Instead I instructed them to save a copy in their user settings folders (still on the same server - not their locals). e.g. Joe Bloggs has a copy of the XLAM in his C:\Users\Joe_Bloggs\AppData\Roaming\Microsoft\AddIns\ folder (the AddIns folders are on the same server as the shared folder which contains the XLAM to install).

That is where the end users and I are currently at.

However users forget/don't bother checking the shared network folder (lets call it: Z:\\shared_files\) to see if there is a new version that they need to copy to their AddIns folder and install.

Which is why I am trying to implement code (that will be called every time the add-in is run) to silently check if the shared folder is found (in case the folder is ever deleted/moved) and if a new version is found, copy the new version to the users add-in folder and install (the tags should kill the old version I expect).

(The second link you provided was interesting but I wouldn't know where to start converting this web updater to network folder updater).

Found an error on the workbook uploaded in the last post.

In the sub 'BackgroundUpdate' I have

Please Login or Register  to view this content.

whereas the procedure in question is meant to return a string value.

(I forgot to update the call to this procedure before I uploaded. The idea is that the procedure should return boolean TRUE/FALSE as to whether there is a higher version number found or not. However if TRUE then it should also capture the name of the XLAM so it can installed in the next procedure called (InstallAddinUpdate). Hmmm. I will probably need two procedures for this...)

you lost me-you don't want to have the add-in installed locally and you don't want to run it from a network?? I'm also not sure what the reasons for not linking to a central add-in are or where they are quoted in the Ken Puls article?

Originally Posted by JosephP

you lost me-you don't want to have the add-in installed locally and you don't want to run it from a network??

I know, I'm not explaining this very well

There are three locations involved:

1. LAPTOP: This is where I work on the master. Occasionally I save a new XLAM with an updated version number and upload it to...
2. SERVER-Shared Folder: This is a central file dump location on a Windows Server. I upload new XLAM files to this folder. I do NOT want end users linking directly to the XLAMs in this folder. Instead I want the users to COPY the XLAM they need from this location to...
3. SERVER-End Users Add-Ins folders: This is Excels default directory for storing Add-Ins for this users server session. (e.g. Joe Bloggs directory would be: C:\Users\Joe_Bloggs\AppData\Roaming\Microsoft\AddIns\) I want the End Users to copy the XLAM from location 2 to here and then INSTALL their XLAM from here (rather than link directly to the file in location 2).

The reasons why I want the above arrangement:

• Occasionally I delete the old XLAM versions from the shared folder. If any end user had installed directly from the (now-)deleted XLAM then they get an error message 'Could not find XYZ.xlam' when they open a workbook. (Whereas if they had copied the XLAM to their Add-Ins folder and installed from there, this wouldn't happen. They would just keep using their (outdated) copy of the XLAM.
• To avoid the update problem described in the Ken Puls article.

The problem with the current arrangement is with the first dot point above. End users forget/don't bother checking the shared network folder (location 2) to see if there is a new version that they need to copy to their AddIns folder (location 3) and install. So I opened this thread to solve this problem.

My goal is for the XLAM (the copy installed in location 3) to silently check the shared folder (location 2) on open. If the XLAM finds a XLAM with a higher version number in the shared folder (location 2) then it will quietly copy the new XLAM from location 2 to location 3 and install from location 3. This way the end user will always have a copy of the latest version installed without any input required on their part.

The major hiccup in this thread at the moment is getting the XLAM to determine whether there is a higher version name in the folder or not. There is some code in AddLoaderV2 that seems promising but I can't work out how to implement just this part of the AddLoader code (UPDATE: The AddLoaderV2 zip is attached to my opening post in this thread. Also download the latest workbook I have uploaded - I have a separate module which contains edited code from AddLoader)

Originally Posted by JosephP

I'm also not sure what the reasons for not linking to a central add-in are or where they are quoted in the Ken Puls article?

The first few paragraphs on the article under the sub heading "Create a procedure to save the add-in to the network drive".

If the information I have supplied in posts #4, 5, 7 is too confusing then maybe it would be easier to ignore what my overall goal is (as it is background info only).

Can anyone suggest solutions to my current problem with this project? (below is recap from post #2)

If someone could please kindly solve the 'CheckFileNameContainsHigherVersionNumber' procedure by integrating the code from the AddLoaderV2 code that is used to determine the latest version XLAM name. (See zip attached to opening post for the AddLoaderV2 code - also I have the main (edited) code from AddLoader as a separate module in my uploaded XLSM on this thread)

Update: I have solved the integration of the AddLoader code. Current test workbook attached. (When testing either change the NetworkFolder constant to the folder the XLSM is currently saved in or just comment out the folder check line in the main sub).

if this is solved please mark the thread accordingly

Originally Posted by JosephP

if this is solved please mark the thread accordingly

Sorry I didn't make myself clear again. I have only solved the AddLoader part of the issue, not the rest of it. (I hope to solve the rest today or tomorrow. I will mark thread as solved after I do).

Stumped. Everything seems to work fine until the code tries to open the copied add-in (line 100 in the sub BackgroundUpdateThisAddIn). Here's what I currently have:

The Background Update module:

Please Login or Register  to view this content.

The code in the post below requires functions from a separate module (couldn't post in previous post due to exceeding the post character limit). To test what I have so far, copy the code from this post and the previous post into a test workbook, change the constant path to a test folder and save as a XLAM. Copy the created XLAM to your test folder and up the version number on the end.

Please Login or Register  to view this content.

Solved . I attach my test workbook for anyone interested. (Please note I haven't removed all my test MsgBoxs).

To test this for yourself, do the following:

1. Download the attached workbook
2. Open the downloaded workbook and edit at least one of the module level constants in the first module (must edit so it points to a directory on your computer (pref. an empty directory to save search time)
3. With the downloaded workbook SAVE AS an XLAM in your User Library Path (this is usually the default location that Excel suggests). Close the downloaded workbook.
4. Browse to your User Library Path and copy the XLAM to one of the folder locations you set in step2
5. Rename this copied XLAM to updatebackgroundv2
6. Open a new workbook, go to Developer->AddIns and install the v1 XLAM. SELECT NO TO THE UPDATE REQUEST (in order to test it properly). Close the new workbook.
7. Now open a new workbook. The v1 AddIn should kick in, detect the v2 file in the test path (step2 in this list) and ask to update.

RESULT: Upon agreeing to install the new version, the v2 add-in will copy to your User Library Path and will install immediately.

TO DO: I need to close the old version of the Add-In, delete it from the User Library Path and uninstall from the AddIns Library. But since I have solved my thread topic - this "background delete" will have to be a topic for another thread. http://www.excelforum.com/excel-prog...ated-xlam.html