Automation is all the rage these days. Automation can thrash labor markets while simultaneously exploding productivity and profits. It will also make human jobs less boring (assuming nosotros withal have jobs). In this tutorial, we will acquire how to use VBA to programmatically download files based on URLs. We might already know these URLs, or nosotros may take to scrape them from the web and parse them. This is automation of a rather deadening job, so implementing it hopefully has a positive impact on your work.

This tutorial is a lilliputian more involved than our recent tutorials. It contains multiple steps and pieces of lawmaking that are outside the general telescopic of a "downloading files with VBA" tutorial. Where it makes sense to explain, we'll explicate, merely in that location are times when we'll simply gloss over the technical parts and just use the code without much caption. This guide lays out the practical requirements for downloading files from the internet using VBA. Once you finish the tutorial, you lot'll exist able to hands download URL files yourself.

With that said, permit'southward go started.

  • Built-in Windows Function
  • Downloading Our First File
  • Downloading Multiple Files
    • Unique Naming
    • Timeout Code
  • Scraping for URLs
  • Ethical Questions
  • Conclusion

The Built-in Windows Library Function

There are a number ways to download files using VBA, including using an XMLHTTP asking to stream the binary data or text associated with your file, and then using the SaveToFile method to finish saving the downloaded file. This is probably the virtually common way to download files, just we're not going to do information technology this way.

In this tutorial, we're going to download files using a Windows function called URLDownloadToFile. The URLDownloadToFile part is part of the Windows library urlmon. Earlier we can employ it, nosotros demand to declare the function and connect to it from VBA. We tin can practice that by placing this line of code at the top of a VBA module:

                          Private              Declare              PtrSafe              Role              URLDownloadToFile              Lib              "urlmon"              _              Alias              "URLDownloadToFileA"              (              ByVal              pCaller              As              Long              ,              ByVal              szURL              As              String              ,              _              ByVal              szFileName              As              String              ,              ByVal              dwReserved              As              Long              ,              ByVal              lpfnCB              Every bit              Long              )              As              Long

Brand powerful macros with our free VBA Programmer Kit

It's easy to re-create and paste a macro similar this, simply it's harder make i on your own. To help you make macros like this, nosotros built a free VBA Developer Kit and wrote the Large Book of Excel VBA Macros total of hundreds of pre-congenital macros to assist you main file I/O, arrays, strings and more - take hold of your costless copy beneath.

I know this is a rather long function declaration. Libraries are basically sets of prewritten lawmaking, usually optimized by the library developer (Microsoft, for this library). All the code needed to access a URL, download the stream of $.25 and bytes, then structure them back into a file on the hard drive is conveniently wrapped into this single function. All nosotros accept to do is throw this annunciation at the pinnacle to brainstorm using it.

Nosotros can see the return type is Long, which means the function volition return a whole number. This makes checking whether the download started much easier. We'll go to that soon, only for now, the about important parts are the szURL and szFileName parameters. These arguments represent the URL source and the proper noun of the file later on we've downloaded information technology. The other arguments can near always be prepare to 0.

Downloading Our Starting time File using VBA

Permit's say yous find a motion-picture show yous similar and you want to download it. You could right-click then "Salve Image every bit…", or you could utilize VBA. Admittedly, using VBA would be inane for a single picture, but if you have hundreds of pictures to download, this chore would be a perfect automation target. This tutorial focuses on downloading images, but the part is only as useful for downloading other file types.

Anyway, we're going to start with just ane picture: a cityscape picture from Hong Kong's Wikipedia page as of November 2018. In the HTML, every flick on every webpage is represented as a link to the picture's very own URL, and that'southward the URL we need to pass to the URLDownloadToFile role. You do not want to laissez passer the URL of the webpage where the movie is embedded.

Under the architecture section on the Hong Kong Wikipedia page, there is a dainty panorama of Hong Kong at night. We tin access its URL by right-clicking and so clicking "Copy Image Location" or "Copy Image Address." The words may change depending on your browser, but you get the thought.

Screenshot of HK Wikipedia Page and the Copy Image Location option of the right-click menu highlighted
Copy the URL of the prototype by clicking the "Copy Image Location" option.

You should have the following image path on your clipboard:

            https://upload.wikimedia.org/wikipedia/commons/thumb/7/75/Hong_Kong_at_night.jpg/2400px-Hong_Kong_at_night.jpg

. If you lot paste this path straight into a web browser's URL box, yous'll become the image on its own page.

To download the motion picture with VBA, we can run a brusque subroutine, like this:

                          Individual              Declare              PtrSafe              Function              URLDownloadToFile              Lib              "urlmon"              _              Alias              "URLDownloadToFileA"              (              ByVal              pCaller              As              Long              ,              ByVal              szURL              As              String              ,              _              ByVal              szFileName              As              String              ,              ByVal              dwReserved              As              Long              ,              ByVal              lpfnCB              Every bit              Long              )              As              Long              Sub              download_HK_picture              ()              imgsrc              =              "https://upload.wikimedia.org/wikipedia/commons/thumb/7/75/Hong_Kong_at_night.jpg/2400px-Hong_Kong_at_night.jpg"              dlpath              =              "C:\DownloadedPics\"              URLDownloadToFile              0              ,              imgsrc              ,              dlpath              &              "HK Skyline.jpg"              ,              0              ,              0              End              Sub

If you have a folder named C:\DownloadedPics\, there will now be a file entitled HK Skyline.jpg in that folder. Simple as that. Find how we concatenated our download path (dlpath) and our file proper noun. Because we practise that, it'due south important to take a abaft backslash at the cease of your download path.

If a folder with the path you specify doesn't exist, your download volition fail. You won't become any fault messages, but your file won't exist downloaded anywhere.

If the download folder doesn't exist, don't worry. Yous tin can make the folder using VBA before calling the URLDownloadToFile role. This arroyo is particularly useful if y'all frequently automate repetitive tasks. For example, you lot can timestamp the binder proper noun when y'all run the plan so you can go back through the folders later and know when yous ran the macro.

Downloading Multiple Files

If you're going to download multiple files, yous likely already have a list of URLs. Lists are overnice, because you can stick them in an array and then iterate through your array with a For Loop. You just need to plan a mode to change the downloaded location'due south filepath each time, otherwise you'll overwrite the previous download with each new file. It's likewise a smart thought to check for delays when downloading multiple files. Nosotros'll explain why momentarily.

Designing with Uniqueness in Mind

If the list of files you want to download is small plenty, you could manually label each URL and use the characterization equally the filename. For example, yous could have 2 columns in Excel: the commencement column volition contain the file proper name and the second cavalcade will contain the URL you desire to download, like this:

Excel Spreadsheet list of cities and download URLs
A listing of cities (labels) and a URL with a photo of the respective urban center

Now we tin can iterate through this listing, downloading each picture show and assigning it a unique proper name.

                          Individual              Declare              PtrSafe              Function              URLDownloadToFile              Lib              "urlmon"              _              Allonym              "URLDownloadToFileA"              (              ByVal              pCaller              As              Long              ,              ByVal              szURL              As              Cord              ,              _              ByVal              szFileName              As              String              ,              ByVal              dwReserved              Equally              Long              ,              ByVal              lpfnCB              As              Long              )              As              Long              Sub              download_multiple_photos              ()              dlpath              =              "C:\DownloadedPics\"              For              i              =              ii              To              vii              imgsrc              =              Cells              (              i              ,              2              )              imgname              =              Cells              (              i              ,              1              )              URLDownloadToFile              0              ,              imgsrc              ,              dlpath              &              imgname              &              ".jpg"              ,              0              ,              0              Side by side              i              Stop              Sub

Since we know we merely want to download jpg pictures in this instance, we hard-coded the .jpg extension in our URLDownloadToFile function. Once downloaded, we should have a folder with files looking something like this:

Downloaded six cities pictures using VBA
The destination binder with 6 downloaded pictures of cities

Timeout Lawmaking to Track Bug

If you lot download several hundred files per run, you lot volition desire to track which files are missing. An like shooting fish in a barrel way to implement a timeout is to check the URLDownloadToFile return value and look a couple seconds if information technology is not the desired result. If it hangs for more than the specified fourth dimension, motion to the side by side URL and mark that file'south download as failed.

A return value of zero (0) means the download has started successfully. Disallowment some connectedness intermission or memory failure downloading a gigantic file, information technology should finish. Thus, a simple yet constructive manner to rails download issues is to bank check whether the issue of the role is aught. If it isn't, effort it one more time before moving on. Other return values are out-of-retentiveness or unreachable-source errors. If you lot want to be very meticulous, you could mark the blazon of failure in your program (hint: use an assortment).

For a more robust solution, you can use VBA to cheque whether or not the files exist before exiting your macro. If they don't, yous know the download failed or it'due south nonetheless downloading.

                          Individual              Declare              PtrSafe              Role              URLDownloadToFile              Lib              "urlmon"              _              Alias              "URLDownloadToFileA"              (              ByVal              pCaller              Every bit              Long              ,              ByVal              szURL              As              String              ,              _              ByVal              szFileName              As              String              ,              ByVal              dwReserved              As              Long              ,              ByVal              lpfnCB              Equally              Long              )              As              Long              Sub              timeout_for_bad_starts              ()              dlpath              =              "C:\DownloadedPics\"              For              i              =              ii              To              7              imgsrc              =              Cells              (              i              ,              2              )              imgname              =              Cells              (              i              ,              1              )              issue              =              URLDownloadToFile              (              0              ,              imgsrc              ,              dlpath              &              imgname              &              ".jpg"              ,              0              ,              0              )              If              result              <>              0              Then              Application              .              Look              (              Now              +              TimeValue              (              "00:00:03"              ))              consequence              =              URLDownloadToFile              (              0              ,              imgsrc              ,              dlpath              &              imgname              &              ".jpg"              ,              0              ,              0              )              End              If              'if the consequence is still zero, mark the failure somehow and move on              Side by side              i              End              Sub

In this example, we capture the returning long integer and check whether or not it's a zero. If it's not, we force the macro to endeavor it again. If it fails a 2nd time, we but requite up and motion on. We don't implement a manner to flag it as failed in this macro, only I recommend you store details nigh the failed download into an assortment before moving on.

Scraping for File Locations

If you want to catch every motion-picture show on a website, you can capture the URLs of the images via webscraping.

Since images will be tagged with img in the webpage's HTML, yous tin use VBA'south GetElementsByTagName function to grab all the paradigm URLs. You can hands adapt our VBA webscraping tutorial to grab the img tag. You will then need to admission the src holding of each Item, which volition incorporate the URL of the paradigm file. If the src holding is not available for some reason, you might be able to use the href holding. At least 1 of these two properties will be present for all images.

The modification from the webscraping tutorial would follow logic similar this:

                          'text = table_data.Particular(i).Children(j).innertext 'Comment the line similar to this and add together the next line instead              imgURL              =              table_data              .              Item              (              i              ).              src

The example we used for webscraping used the Wikipedia page on country and dependency populations, which has a nice table of countries with ranks, populations, and fiddling flags. To practise, attempt to scrape the tabular array, extract the country name, scrape the associated flag'southward prototype URL, download the flag picture file, and use the extracted country name to proper noun the file. Paste your solution in the comments section!

If you lot aren't and then ambitious, you could apply something like imgname = i & downloadPath, where i is the for-loop control variable. This method would friction match the flag with the country's population rank, so at that place is some practicality to doing it this simpler way.

If y'all're lucky, the photos on the website might have a title property, and you could employ that for naming, too.

Upstanding Questions

You tin automate the download of hundreds or thousands of files rather easily using this method, specially when coupled with a webscraper that's simply looking for a tag, like the img tag, to take hold of whatsoever and all files on a website.

There are three ethical bug hither.

Offset, you'll be requesting server fourth dimension from someone or some visitor, and automating this process might put strain on their infrastructure. In the extrinsic instance, you might become your IP banned from accessing the server as a punishment, but even if you don't, there's the ethical question of using up resources others are providing for free and fair apply. For huge sites, like Google or Amazon, they probably won't notice at all. But smaller companies, peculiarly those running their ain servers, might be afflicted.

The 2d ethical question revolves around hotlinking and revenue theft. Basically, if you already know the epitome URL and don't need to visit the principal webpage (such as if yous found the image through Google'south Prototype Search and just grabbed the URL), y'all'll be depriving the host of any advertising revenue. On one mitt, you won't be taking up server resources to load the main page, but you also won't exist rewarding the host with any advertising revenue or traffic in exchange for their information. This could impairment their search engine rankings.

While this kind of automation probably doesn't bear ethical questions of eliminating someone'southward job, it does carry some ethical implications. You should think about such ethical considerations before automating whatever task.

The third ethical question, and the 1 that plagued me the most before posting this tutorial, is that someone with criminal intent could use the function for more sinister macros. For example, someone could write a macro to automatically download viruses or other nefarious file types to someone's motorcar. I'thou posting this tutorial because many people would benefit from being able to automatically download files using VBA. Don't be a jerk and abuse the knowledge you've learned as a VBA developer.

Conclusion

In this tutorial, you lot learned how to utilise VBA to download files. Nosotros used images in our examples, only you tin download any file type. The unabridged automation process can be quite long and may require a lot of research, only this tutorial will go you on your way. Automation can assist reduce irksome work, but always remember to consider the ethical implications that arise from automating work, from job elimination to revenue theft.

I hope y'all institute this helpful. When you're ready to take your VBA to the next level, recollect to subscribe using the class below.