This is a cracking blast from the past, I was digging through my old personal macro excel backups when I came across this beauty.
It dates back to my recycling plant development days, I had to devise an algorithm to work out the most economical site placements for food waste recycling facilities for a given highly dispersion of waste arisings.
The data I had to work with was in the form of a list of postcode locations along with annual waste tonnages. I had originally intended to lookup the addresses against my longitude/latitude list however turns out the maths to convert long/lat into something usable in excel was rather complex.
I've since worked this long/lat conversion out and have been meaning to write it up into a blog post but it is frankly pretty boring even by my standards.
So back then my next angle of attack was to use a screen scraper - a bit of code I could fire the postcode off into the interweb and get back the result I needed. I already knew the site I was going to leverage, Streetmap.co.uk, as I'd been using it for a while for all things MTB as it was it was the only service at the time where you could view Ordnance Survey maps.
Paste this code into your VBA Project:
Then all you need to do is place you cursor in the cell next to the postcode you want to look up and run the macro.
If you have a list of postcodes the code will happily rattle down the list for you.
But what use is it to me I hear you ask? Plenty, I wasn't expecting you to need a recycling plant location algorythm, it has lots of uses: