How to check Last Modified Date on Temp File and compare it to original/parent file?

[CaffeinatedCoder]

This is a bit hard for me to explain, so please bare with me if you will.

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?

[LJMetzger]

Hi CaffeinatedCoder,

Coffee or Mountain Dew? See https://www.math.utah.edu/~yplee/fun/caffeine.html

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

I don't know the answer to the 'Last Modified Date' question, but I do have an alternative.

a. Install a 'Software Version' somewhere in your add-on.
b. Create another file (text file or Excel file on the server) that contains the Expected Latest Software Version number. This file could also contain a message.
c. Each time the add-on does something, check the versions. Instead of opening the 'other file more than once', the 'Other file 'Last Modified Date' could be saved and checked to improve speed performance.
d. When the user add-on is out of date, you could display a message in a MsgBox to include the optional message in the 'other file'.

I have successfully used this 'canary file' approach many times.

Good luck.

Lewis