0 votes
in VBA by Skilled (605 points)
Hello everyone

I have a workbook with the name "My Project v.1.0" and I would like to upload it to any file hosting website

The workbook will be with many users so I would like to put a code in the workbook open event so as to check the version that is already there on the user's device and compare the existing one on the link I will upload to and if the version is different display a message to the user that there is a newer version ..and offer to download the newer version

Any idea how to do such task ...?

1 Answer

+1 vote
by Super Expert (2.4k points)
selected by
 
Best answer

This is exactly what I do with my Check for Updates feature on wellsrPRO. I don't think I want to publicly publish my full working macros, since it's kind of a staple of how wellsrPRO works, but I do want to give you the detailed steps I took. Hopefully it'll help you come up with a solution:

  1. Upload 2 files to your web hosting site
    • The first file will be your workbook
    • The second file will be a text file of some sort that contains the latest version number. You'll update the version number in this file each time you upload a new workbook.
  2. Store a global constant variable in your file with the version number.
  3. Under the workbook_open event, you'll use a VBA HTTP Get Request to read the text file containing the latest version number.
  4. If the version number is greater than or equal to the version stored in your global constant variable (the version the user is currently running), display a message box telling them a newer version is available (vbYesNo).
  5. If the user clicks yes, download the file to a standard place with something like the VBA URLDownloadToFile method.
    • I recommend creating your own folder with MkDir if one doesn't exist under the %ProgramData% folder, which is a pre-defined environment variable for people. You can determine that path with the VBA Environ function, like this: Environ("ProgramData")
  6. This is where it gets tricky. Once the download is complete, you'll need to replace the open copy of their workbook with the new copy. You can't do this while their workbook is open, so you have to somehow close it, replace the file, and open it again. Here's how I do it:
    • I call a vbscript file I package with my wellsrPRO add-in, but you may find a better solution. 
    • You'll call this script with a VBA Shell command, and you'll need pass this script the path of the new file you just downloaded and the path of the workbook you're about to close.
    • The script will close the open workbook then use VBA FileSystemObject commands, like FSO CopyFile to overwrite the file the user had open with the new file you just downloaded
    • It will then open the file again. This time, it will be the latest version. I open it in the same path the user has open so they'll know where it's stored.

I hope you found this helpful and I would love to hear if anyone has a better solution. This solution works great, but sometimes anti-virus programs block the download of the workbook because of the way it calls the vbscript. It flags it as a "generic script." I haven't found a reliable homegrown way of preventing this. 

I'd be very curious to hear what you're working on. :-)

 

 

by Skilled (605 points)
That's amazing and awesome Mr. Ryan. This solved most of the problem ..

Now as you pointed there are 5 points are perfect and solved

As for the point number 6, I have no idea how to start it .. Can you point me to a link to have an idea first about that topic?
by Skilled (605 points)

I tried to download the wellsrPRO addin but I couldn't ... I have downloaded it previously but the old hard was damaged and I lost it ..

by Super Expert (2.4k points)
+1
I just sent you an email with a direct link to download the latest version. :-)
by Skilled (605 points)
Thank you very much.
by Skilled (605 points)

@wellsr

Hello Mr. Ryan

I have solved the problem with the help of a topic at chandoo at this link

https://chandoo.org/forum/threads/convert-vbscript-to-vba-copy-and-replace-activeworkbook.42487/page-2

Have a look at post #37. This is the final solution for me

Now we can do without the vbscript file. Have a look and please if you find any improvement to the existing approach, please don't hesitate to post it for us

Best and Kind Regards

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...