Macro to check for newer version on workbook open

[leonnafaye]

I have an Excel program that I maintain for my company that needs to be updated occasionally. Currently, I send out any new versions of the Excel program by email attachment. However, people rarely check their emails, and I'm afraid most people are using an outdated version. I would like to create a Macro in Excel that runs on Workbook open that checks something online that contains the correct version number, and checks to see if it is higher than the user's version. The only thing I don't know how to do is the "check something online" part. I'm not able to upload the actual Excel file online because it contains proprietary information. I thought about using a Google Spreadsheet that contains the newest version number in a cell, and somehow referencing it.. but it seems it's very cumbersome to do that. Does anyone have any ideas of how I could accomplish this?

[Tsjallie]

Hi Leonnafaye,
want you want can be done, but requires some programming.
But there may be a more simple way to achieve what you want: suppose you would always issue a new version of your program each month (or other frenquency). Then you could simply check on workbook_open wether the program is older than 1 month. And if so, display a message urging the user to use the newest version. If your users are very undisciplined you could even disable the version their are using and thus forcing them to use the newest one. Alternatively you could give each version an expiry date to be more flexible.
Sounds a bit rude, but will definitely work.

On the other hand, if you wish to embark on the trail of online checking I would be happy to work with you on that.
This Google spreadsheet could be one soltion, but there are more ways to achieve this.
It does however require that your users are connected to the internet when starting the app.

[leonnafaye]

Thank you so much for your help!

I, too, thought about putting in a date checker on open but the updates do not come at any certain interval.. it could be 1 month or 6 months. So, that wouldn't be reliable.

What other ways could this be accomplished? Thank you again for your help.

[Tsjallie]

I was hoping you would say that
I already looked into things and how to accomplish checking for updates thru internet and it seems easier than I thought.
Made a dummy workbook which logs in to Google Drive and opens a Google spreadsheet.
I'm now looking to set up thing so I can share them with you. Maybe we can set up a dummy google account which we can use for sharing and testing things.
I'm now in the office. Will come back to asap.
In the meantime, can you think about how you want things to work. Are there any constraints or other things I should take into consideration?

[leonnafaye]

You're a lifesaver!!

One issue we might run into is that Google Drive is supposed to be blocked on my company's intranet.. however I've always been able to access it, but I don't know if others have problems accessing it. I have a friend at work who can test it with us and see if we run into that issues. Secondly, I don't want a browser to open when the macro checks online.. all users will be connected to the internet so hopefully we can avoid that.

This is how I would like it to work:

1. User opens the Excel file.
2. Macro checks immediately if the user's version is older than the newest one.
3. If the macro finds that the user is using the current version, it does nothing. No msgbox or anything.
4. If the macro finds that the user is using an OLDER version, a msgbox comes up notifying the user they are using an older version, and prompts them to request the newer one. (I already have a macro assigned to a button that requests the newest version, but nobody ever presses it.)

[Tsjallie]

Hi Leonnafaye,
here's a first try. Of course it's just a prototype, but I think it shows what's possible (and that's much more than it does now).
From the sheet you can run a macro CheckForUpdates.
Before you do that you will have to fill in the credentials for accessing the versioning spreadsheet at Google Drive.
I will send you these credentials via PM.

Hope its about what you're looking for.

[leonnafaye]

Did I mention you're amazing?!


Yes, that's exactly what I'm looking for. However, I'm running into a snag. I changed the login to my own login to a spreadsheet to which I put the current version number (9.2) in field A1. I changed the macro to reference A1 in this line:

If CurrentVersion = oWB.Application.Range("$A$1") Then

And I also set 9.2 in this line:

CurrentVersion = "9.2"

So it should just do nothing right? But I still get the "you're not using the latest version" message. I also reference A1 in this line:

MsgBox ("Latest version is " & oWB.Application.Range("$A$1")) 'Proof that the Google spreadsheet has accessed successfully

And it DOES tell me 9.2 in that msgbox. What could be wrong here?

[Tsjallie]

Did you republish the spreadsheet after changing it? If not the macro will still read the previous data.
I get the same when I change the version in the spreadsheet without republishing. As soon as it's republished it works fine again.

[Tsjallie]

It seems however that after some kind of timeout it publishes automatically.
It does however have to be published intially. Not sure if you did that for your version spreadsheet.

[leonnafaye]

Ok, I am on my work computer now testing it. I have published and republished the worksheet, and made sure that I set the CurrentVersion to the same version on Google, but it still gives me the "You're not using the latest version" msgbox.

Ignore the attachment.. I don't know how to delete it. lol

[Tsjallie]

I erased my previous post.

Could you pls make a change in (I believe) line 46:

If WorksheetFunction.Text(CurrentVersion, "#0.0") = WorksheetFunction.Text(oWB.Application.Range("$B$4"), "#0.0") Then

This makes sure the macro is comparing a string value to string value.

[leonnafaye]

Ok, before I try anything else, I just had my friend test the spreadsheet on our work intranet and he gets an error saying the Google document cannot be loaded.. I think our network is blocking it. It's really strange that I can access it but others on my company's network can't. I'm so sorry... you've helped me so much on this. Would there be any other way to do this without using Google Drive?

[Tsjallie]

I just had my friend test the spreadsheet on our work intranet and he gets an error saying the Google document cannot be loaded

I didn't notice it before but I got a warning by Google that my account has been accessed from an unusual location So maybe the blocking has come from Google iso you company.

[Tsjallie]

Hi Leonnafaye,
I think there can be other solutions. I'll come back on that.
But maybe you can check with your IT dept if they indeed blocked Google Drive (which I can imagine) and if may be they can come with some alternative.

[leonnafaye]

I think it probably gave you that warning because I tried to log in with the credentials you PMed me and it said the computer was not recognized, and it wouldn't let me continue without answering the security question.

But anyway, I checked with my IT guy and unblocking Google Drive is definitely a no-go, and he was flabbergasted as to why I can access it but no one else can. Could we try another file sharing site? I know AT&T Locker works on my intranet.. which may give you an idea of what company I work for... lol

[Tsjallie]

.... and unblocking Google Drive is definitely a no-go ........

That doesn't supprise me as Google Drive is protected by only a userid and password. And that's not much of security

I will look at AT&T Locker and see if we can do something there. I'm also looking into the article Buran posted to see if that offers a solution.

In the meantime I feel I must put a challenge to you:
we started out on this with your problem of your users refusing to upgrade to newer versions and ignoring your email notifications.
In order to have the solution we are working on ever to go live, you will have to figure out a way to get the solution to the machines of your users.
And if you would succeed on that, how will you get them to upgrade?
Just to keep you busy

[Tsjallie]

I will look at AT&T Locker and see if we can do something there. I'm also looking into the article Buran posted to see if that offers a solution.

OK, I have looked AT&T Locker. Looks very much the same as Google Drive. Same security. Do I smell politics?
Difference however is that you can only download documents stored there and - more important - the login page is a FlashPlayer app and I'm affraid that needs java coding to access it. For me that's end-of-the-line as I'm not at all familiair with Java. Also the post by Buran doesn't help on this.
So, if you want to use AT&T Locker you will need someone who's Java savvy.

[buran]

Here is useful link that gives "ready" solution
http://www.jkp-ads.com/Articles/UpdateAnAddin.asp