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:- check if the shared network folder path can be found at Workbook_Open (in case anything happens to the network folder path)
- 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)
- 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)
- load the new Add-In from the users Add-In folder
This is what I have so far in ThisWorkbook:
Option Explicit
Private Const strNetworkFolderPath = "C:\upload" 'to put correct location in later
Private Sub Workbook_Open()
'START BACKGROUND UPDATE CHECK PROCEDURES
If NetworkFolderPathIsValid = True Then
'If FolderPathContainsFileSimilarToThisWorkbookFileName = True Then
'If CheckFileNameContainsHigherVersionNumber = True Then
'Call InstallAddinUpdate
'End If
'End If
End If
'END BACKGROUND UPDATE CHECK PROCEDURES
'Call CreateControls 'I will add the Add-In details later
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Call DeleteControls 'I will add the Add-In details later
End Sub
'
'==============the background update check subs/functions will go below=============
'
Private Function NetworkFolderPathIsValid() As Boolean
NetworkFolderPathIsValid = False
If Dir(strNetworkFolderPath, vbDirectory) = "" Then
Exit Function
Else
NetworkFolderPathIsValid = True
End If
End Function
Private Function FolderPathContainsFileSimilarToThisWorkbookFileName() As Boolean
FolderPathContainsFileSimilarToThisWorkbookFileName = False
Dim sFile As String
sFile = Dir(strNetworkFolderPath & "*.xlam")
Do While sFile <> ""
If Left(ThisWorkbook.Name, 12) Like Left(sFile, 12) Then
FolderPathContainsFileSimilarToThisWorkbookFileName = True
Exit Function
End If
sFile = Dir
Loop
End Function
Private Function CheckFileNameContainsHigherVersionNumber() As Boolean
CheckFileNameContainsHigherVersionNumber = False
'Somehow implement the version number code from AddLoaderV2 by Charles Williams, Decision Models Ltd
End Function
Private Sub InstallAddinUpdate()
Dim strNewAddInFileName As String
Dim strUsersAddInFilePath As String
'HELP! I NEED CODE TO DO THE FOLLOWING:
'1. Set the filename of the updated XLAM found in the network folder to strNewAddInFileName
'2. Copy this XLAM from the network folder to the users Add-In folder path (use Environ?)
'3. Set users add-in folder path to strUsersAddInFilePath
'4. see below for what I have for the installation
AddIns.Add Filename:=strUsersAddInFilePath & "\" & strNewAddInFileName
AddIns(strNewAddInFileName).Installed = True
If Val(Application.Version) <= 10 Then
Application.Run "'" & strNewAddInFileName & "'!Auto_open"
End If
'the new add-in should automatically kill the old add-in installed as they use the same tag name
'(The 'delete previous version' code will run before installing the new version)
End Sub
'========================================================================================================
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?)
Bookmarks