Download an excel file from url

Until now I was only writing about HTTP methods, but did not really describe the final trick to download the file from URL. In today’s article I want to present You and compare two methods of downloading files from URL – URLDownloadToFile function and saving byte array to file method.

I don’t want to copy definitions from other websites or rewrite them with other words. I want You to present it my way, how I understand this and how I deal with that.

URLDownloadToFile function

This was the first method I have ever learn to download file from URL. Most likely because that is its function name.

Firstly You need to declare this function pointing out which and where this comes from.

So how to read that correctly?

• You declared function called URLDownloadToFile from urlmon.dll library file.
• This file is somewhere in C:\Windows\System32 system folder.
• You referred to URLDownloadToFileA from this library.
• Your function has 5 variables – 3 Long type and 2 String type.
• URLDownloadToFile returns Long type value.

How I use this?

The best example is the code. Let me show You also below.

• This functions returns Long value, so xstatus variable must be declared as Long type.
• myURL has to be String type variable, link to website.
• filepath has to be also String, which represents new path for download (path to folder + name of file with extension).

This is enough information You need to use this function. If You feel it is not, please go to other pages like this.

Saving byte array to file method

This method requires to create Http object like XMLHttp or WinHttp as first, open the URL and send request to the server like it was in article.

Secondly, create FreeFile as Long type, byte array and put new file path into String.
Then put inside byte array .responseBody property of http object. This step gets the file from object and puts inside the array.

The last thing You have to do is create that FreeFile in given file path and put inside that byte array.

Little bit complicated at the beginning, but not that hard in the end.

Summary

That’s it! Those were 2 methods to download file from URL, which I was and I am using in my macros. Which one is yours? If You have other ways to download file via VBA please feel free to write about this in comment section 🙂