Excel Front End Auto update

[chinaboy]

Hi Guys,

I have an Excel workbook that I use as a Front End to enter data to a Access database. Each user has their own copy of the workbook on their desktop. I would like when the workbook is open it would automatically check to see if the user is using the most updated version. My plan is to
store a version number in the workbook on a hidden sheet. Store a text file with the latest version number on our shared drive. Include a macro which automatically reads the latest-version-textfile and compares the version number to the one in the currently running workbook. If the version the user is running is not current I want a message-box telling the user that their workbook needs to be update. At this point the user clicks update and the workbook is closed and replaced by the current workbook which will be stored on our shared drive. Then automatically re-open.

I have used something similar for a front-end update in Access, but I can't find a solution for Excel. Does anyone have a sample code or procedure to make the above work? Fairly new to VBA, but will a bit of guidance I think I can pull it off.

Thanks in advance.

[JapanDave]

Where is the database stored? And determines that the version is current?

[chinaboy]

The Access database is also stored on our shared drive. I am not concern with the version of the database. Only want to make sure the users have an updated version of the Excel workbook. I hope I answered your question.

Thanks

[JapanDave]

In that case why don't you load each work book with a piece of code that detects the database?
Something like this perhaps?

Please Login or Register  to view this content.

[chinaboy]

Thanks for your response. Can you explain what the code below will accomplish? I am a bit confused.

[chinaboy]

Hi...I found this code which I think is doing what I am trying to accomplish. But I am getting a compile error: sub or function not defined at the line "Call CKV(currentv) " Also I am using just a simple .txt file instead of a .vi file. Will this still work?

Below is the full code that I am using. I place the code below in its own module, not sure if this matters.

Please Login or Register  to view this content.

[JapanDave]

It does exactly what the function states, it detects if the database exists. So any workbook without this code is not current. Put the path of the database in this line and run the function. If the database path is incorrect it will also give you an error.

Please Login or Register  to view this content.

[chinaboy]

I guess that why I am confused. The database will always exists. The database will remain intact. The only thing I will be updating is the Excel file. So regardless a user will be able to connect to the database but may not have the same functionality when using older version. Sorry if I don't get it. I am really new to the vba thing. But thanks for your help.

[JapanDave]

Maybe I am not understanding the question? How are you updateing the workbooks? Are you doing it yourself or is some code doing it when a user does something?

[chinaboy]

I will be making the changes to the workbook. Then I send the updated copy to each user. If I make changes to the workbook, I will place the new copy in a folder on the shared drive. I will change the version# in a text file. Once a user opens an old copy of the workbook, it should first check the text file to make sure that the version matches. If it doesn't match, then it should prompt the user then close the workbook then delete the old file and replace it with new file and reopen the workbook. Goal is to automate the process of updating any old copies of the excel workbook without having to manual send it to each user.

[chinaboy]

JapanDave...I got the routine in the previous post to work. Thanks for you help with this.