Use VBA to download zip file and unzip the file?

[VAer]

For example, if there is a zip file https://www.test.com/data/zip/sample.zip

How can you use VBA to download the zip file then use VBA to unzip the file?

Thanks.

[LJMetzger]

Hi VAer,

Here is a solution courtesy of Ron de Bruin: http://www.rondebruin.nl/win/s7/win002.htm

Lewis

[Leith Ross]

Hello VAer,

I wrote this macro to download a zip file from a website or local storage. What I found was the URL for a zip file on a website opens a download page and then the server downloads the file to your download folder.

The macro works on my local intranet and local storage with no problem. So, perhaps you will find it useful. The API code used is for 32 bit Windows and Office. If you experience a problem with the API call then I will need to provide you with the 64 bit API call.

Download and Unzip File

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. Leith for this awesome code ..
I have tested it like that

Please Login or Register  to view this content.

but I got empty folder with no files ..
Can you provide me with a valid lonk for a zip example file to make sure the code is working well? and why it is working but give me empty folder with no files?

[Leith Ross]

Hello Yasser,

I encountered this problem as well. When downloading a sample zip file that I uploaded to my MediaFire account. The problem lies in how the server handles the link. When clicked, the server opens a new page which forces the user to click again to start the download.

The API call will download files like pictures from a website with no problem. These files are directly accessible from the server. If the file cannot be directly accessed from the server then additional automation is required to negotiate with the host server to obtain the file.

You can test the macro on a zip file on your hard drive. It will unzip the file to the folder path you provide.

[YasserKhalil]

Thank you very much for reply
I had the direct download from the mediafire .. and tried another link and got the same empty folder
Can you give me a working zip example file to test it?

As for the zip file on my hard drive it is working perfect

[Leith Ross]

Hello VBer,

The failure of my first macro to download and unzip a file led me to dig into the Windows API a little deeper. I have created a new macro using a different API that works.

Here is the new macro code and sub that will download a zip file from a website to your desktop.

Please Login or Register  to view this content.

[YasserKhalil]

Hello Mr. Leith
I have tested that and I encountered an error run-time '75' (path/file access error) at this line

Please Login or Register  to view this content.

[Leith Ross]

Hello Yasser,

Try setting the download folder path to something other than your desktop.

[YasserKhalil]

That's great. Now it is working well
But why the path of desktop failed .. Are there any settings that can be done to allow this path instead of the error access?

[YasserKhalil]

I have tested again and found that it succeeds now to download to desktop .. That's too weird
Now it is working perfectly
Thank you very much Mr. Leith

[Leith Ross]

Hello Yasser,

That is strange but I am happy to hear it is now working for you. If you discover any problems, let me know.