Is there a way for a temp file Excel Add-on to know if it's public read-only file has been updated since the end user has opened up Excel?

Question

I have an Excel Add-On (.xlam) I've created for my department at work. I have it set up like so:

1. There is a public (Read-Only) version of the Add-On on the company's shared network drive. This is the file that all department personnel have installed on their computers.

2. In order to ensure the public version is installed correctly on the employee computers (not saved locally in a user's Add-On folder) I created a vbscript that automatically installs it for all users the right way, which allows me to properly and effortlessly update the add-on in the future.

3. I have a development version of the Add-On installed and stored locally on my computer in which I am able to make changes and test updates before deploying them to the public version on the shared network drive.

4. When I'm ready to deploy an update I have a Sub that turns off the Read-Only attribute of the public file, overwrites the file with a copy of the development version (itself), then turns the Read-Only attribute back on for the newly updated public file.

#4 Can be done regardless of whether or not any user is currently using the Add-On at the time I'm updating since the public file is Read-Only. Being Read-Only, when a user opens an Excel instance what they're actually using is their own temp copy of the public file that is saved in their temp folder.

The only pitfall to this method for me is when I have to deploy a hotfix in the middle of the day. If a user already has Excel open when I deploy the hotfix they have to restart Excel in order to have the updated version. This means having to send out an email blast and is dependent on the users seeing the email in order to know they have to restart Excel for the changes to take effect. I'd much prefer having the Add-On check if it's updated routine and alert the user via a MsgBox, which would instruct them to restart Excel. I have an idea of how to do this, but am not sure if it will work as intended.

Now for my question.

Is there a way for me to have the Add-On check to see if it's the most recent version by comparing the user's temp copy's Last Modified Date and the public add-on's Last Modified Date or will they both have the same date?

I know I'd have to use OnTime to schedule the routine checks, but I'm not sure if looking at those two dates would be accurate.

If that won't work how else could I check if the user is using the updated version?

Answer 1

I would add an extra worksheet to your add in and use ExecuteExcel4Macro to check for version updates.

---

Public Sub CheckForUpdates()
    Const MasterPath As String = "C:\stackoverflow"
    Const MasterFileName As String = "MasterFile.xlam"
    Const WorkSheetName As String = "Settings"

    Dim Msg As String, Version As String
    Dim ButtonNumber As Long

    Version = getVersion(MasterPath, MasterFileName, WorkSheetName, 2, 2)

    With ThisWorkbook.Worksheets("Settings").Range("B2")
        If .Value <> Version Then
            Msg = getVersion(MasterPath, MasterFileName, WorkSheetName, 4, 2)
            ButtonNumber = IIf(getVersion(MasterPath, MasterFileName, WorkSheetName, 3, 2), vbCritical, vbInformation)
            MsgBox Msg, ButtonNumber, "Update Available"
        Else
            Application.OnTime Now + 4 / 24, "CheckForUpdates"
        End If

    End With

End Sub

Function getVersion(MasterPath As String, MasterFileName As String, WorkSheetName As String, RowNumber As Long, ColumnNumber As Long)
    If Right(MasterPath, 1) <> "\" Then MasterPath = MasterPath & "\"
    getVersion = ExecuteExcel4Macro("'" & MasterPath & "[" & MasterFileName & "]" & _
                                    WorkSheetName & "'!R" & RowNumber & "C" & ColumnNumber)
End Function