How to tell user to get updated copy?

[Paul_Hossler]

We have a 'Gold Copy" of the latest version of a standard WB on the server with the latest formats, VBA, data, etc.

User's can pull down their own copy to their PC. They can't work with the server copy.

It'd be really helpful if a previously downloaded copy of the WB could 'call home' to check if they're still the latest, and notify the user to update the WB from the server if it's been updated (File mod date, Ver num, etc.)

For now, it'd be a manual update by the user, but if they opened an old copy on their PC, at least a reminder or suggestion to get the latest version off the server would be helpful.

Does anyone have some VBA or ideas to help?

Paul

[geekgirlau]

I'm sure there are lots of ways to do this, but one approach might be:

Version number recorded in the document properties in the workbook
Version number recorded in text file on server
Compare the 2 in the Open eventOf course you could always save the workbook as a template in a central location and have users access it as a template.

Another idea is rather than have your users manually create a copy, create a batch file that copies the "gold" version of the workbook to their local folder, then opens the file. The shortcut to run the workbook points to the batch file rather than the actual workbook itself.

[Bob Phillips]

Or have a database on the server that they can read.

[Paul_Hossler]

Thanks for the ideas --

These things get pull down, copied, and re-used by the users.

So I was thinking some WB_Open code to "phone home" to the server and read a version number or modification date of the server copy (assumed to be the most current), and notifying the user "You're out of Date" would the simpliest way. No special CM database, etc. Just update the 'Gold Copy" on the server, and the next time some one opened their WB they'd see the notification.

Don't have any special permissions on the server, except to load files into a specific folder.

I figured that someone must have crossed this bridge before, and would have some simple ideas or VBA that I could work with.

Paul

[Bob Phillips]

Paul,

You seem to fully understand the principles involved, so wht are you looking for help with?

How do the users pull this Gold Copy DB?

[Paul_Hossler]

Users just copy it from a shared folder on the server, nothing sophisticated at all. Just trying to come up with a way to tell them it's time to go and get the latest copy.

Before I tried to roll my own, I thought I'd see if some one else who's been down this road had any code or ideas that they'd be willing to share, at least as a starting point.

I'm sure (100% !!!) that I wouldn't think of everything that should be addressed to increase it's bullet-proof-ness.

Paul

[MWE]

Users just copy it from a shared folder on the server, nothing sophisticated at all. Just trying to come up with a way to tell them it's time to go and get the latest copy.

Before I tried to roll my own, I thought I'd see if some one else who's been down this road had any code or ideas that they'd be willing to share, at least as a starting point.

I'm sure (100% !!!) that I wouldn't think of everything that should be addressed to increase it's bullet-proof-ness.

PaulYou did not mention if you wanted this capability because it would be useful or because you are operating under FDA/GMP or ISO or some other "oversight" and it is REQUIRED that users use the most recent (approved) version. If it is just a good idea (and it is), then any of the methods suggested above will work. If it is required, then you have to build in some sort of fail safe mechanism. For example, most automated methods assume that macros are enabled when the user starts up his/her copy of the WB. If they decide to not enable macros, then what?

One further thought: if this is a one time, one application effort, most methods will work. If this is something you want to do for other WBs, other applications (e.g., Word templates), etc, then consider approaches that will work across multiple file types.

[geekgirlau]

If you're looking at Excel, one thing you can do is hide all sheets apart from one that tells the user that they need to have macros enabled. If the macros are enabled, the first thing your code does is hide this message sheet and show everything else. If macros are not enabled the user sees the message.

Not sure how you could do something similar in Word though ...

[MWE]

If you're looking at Excel, one thing you can do is hide all sheets apart from one that tells the user that they need to have macros enabled. If the macros are enabled, the first thing your code does is hide this message sheet and show everything else. If macros are not enabled the user sees the message.

Not sure how you could do something similar in Word though ...This is an interesting idea. It should work the first time the user uses the target WB. It you want to have it work again (I assume that the user will use the local copy of the WB many times), you would have to have an On_Exit (BeforeClose for xls) macro that would return the WB to the previous state.

Any of these approaches would also require that all potential users have macro security to Medium (it is my experience that most companies roll out MS office with macro security set to High or Very High).

[Paul_Hossler]

Thanks everyone --

It's for Excel only, and since the template-based WB uses macros, users are 'trained' to enable macros when they open it.

We usually use a template naming convention like

\\Server1\GoldFolder\ApplicationX_ver2.14.xlsm (file://\\Server1\GoldFolder\ApplicationX_ver2.14.xlsm)

I had though about looping through the XLS's in the 'Gold Folder' and testing Create Date or file name. If there were a way to read the file Property (e.g. The Details that come up when you right click a file and select properties) I could test for one of these.

I was looking to see if anyone had some code or a technique to share that had some of the bullet-proofing incorporated that i'd have to learn the hard way when a user called to complain. Things like "Server Not Available" or "Missing Folder" etc.

Paul

[geekgirlau]

There is a dll that enables you to read the document properties of a Word document without actually opening it; can't recall the name off the top of my head, or whether there is an Excel version available. However worth asking Mr Google. Of course you could also open the file to read the properties (slower, but it works).

In terms of checking for availability of the file (server down etc.) most times a simple DIR will give you the answer to this. If DIR does not return the name of the file correctly, it is not available.

[Bob Phillips]

You can use FSO (FileSyetemObject) to get those properties.

[Paul_Hossler]

Originally I was using FSO's GetFileVersion method, but I could never figure out how to save an XLSM with an 'extractable' version number, or to change one that was there. Appeared like you could only set Version on a selected type of file (exe's, dll's etc.)

If there's a way to do that, then I think I'm there because that will give me a sure way to determine the version. The rest is easy.

Right now, I'm relying on the file naming convention. I test the server folder files' name to see it there's a file with a name "greater than" the open WB. Example, if 'MyTemplate v2.01.xlsm' is open, and ''MyTemplate v2.04.xlsm' is on the server, I can put up a message.

Question now is 'How do I set the Version Number' of an XLSM?

I think I getting close.

Thanks

Paul

[Bob Phillips]

I don't think an XLSM will have a v ersion number, that refers to the version of an executable.

I tend to store the version number as a document property, I have code to auto-increment it, but that is not exposed by a closed workbook as far as I know.

[Paul_Hossler]

Thanks

Yea, that's where I ended up when I tried to go the version number route.

I didn't want the users have to open the server WB to read a property each time because sometimes opening an WB on the server takes a 'long' time, and you know how impatient users can get.

I did consider using the Time hours/minutes part of the CreateDate to encode a version (02:34 = ver 2.34), since I can get to that with FSO. But decided that it wasn't good enough since it was on a server and might not be left the way I set it, as well as extra work each time by me to manually set it (one more thing to remember).


Guess the best idea so far seems to be to use a file naming convention. That's certainly not fool-proof, but seems like the best compromise I can see ... at least for now.

Paul

[mdmackillop]

You could save details in a small text file on the server. This would be simpler to check than opening a workbook.

[Jan Karel Pieterse]

This sounds a bit like what I did here:
www.jkp-ads.com/articles/updateanaddin.asp (http://www.jkp-ads.com/articles/updateanaddin.asp)
I think having a small text file with the current version number is probably easiest.

[Paul_Hossler]

Jan -- that's a very good writeup / explaination, lots of good ideas

I really like the add-in idea. Now I have to figure out if I can make the WB template into an add in

Thanks everyone

Paul

[JP2112]

You could also save settings to the registry using the techniques found at

http://www.jkp-ads.com/Articles/DistributeMacro08.asp

--JP