Using VBA to pull driving distance between two zio codes

Good morning!
I have a worksheet containing 3122 Zip codes. Those are the only zip codes I need to worry about. The beginning zip code will always be our plant. I'd like to be able to plug in a formula in another workbook to retrieve the driving distance between our plant and any of the destination zip codes in that sheet. Currently I'm trying to populate the worksheet with the driving mileage between the plant and the list of zip codes. I found this macro on the web and made a couple of changes. When I run the macro it just spins it's wheels. I've never tried to call a web page for data with VBA. I fear that I am over my head on this one.
Any input, assistance or suggestions are very much appreciated.
thanks much!
Bill

Please Login or Register  to view this content.

Alansidman,
Thanks much for the link. I looked into that page and unfortunately they require either an address or coordinates. The only absolute that I am going to have going forward is zip code. I do not want to get a personal API Key and associate it with a work project. I will continue to look for a solution using zip codes.
Thanks much
Bill

Kind of a stone age solution, but you could always have an admin, temp, or intern look up each zip code manually and populate a spreadsheet with the driving distance. Assuming an average of 30 seconds per zip code, it would take about three 8 hour workdays to complete.

Or, perhaps write code in your worksheet that checks your list of zip codes for the driving distance, and if it hasn't already been entered then a message box directs the user to the website to look it up manually and enter the driving distance, which would then get written to the sheet with the zip codes. That way, you build your database on an as-needed basis, and only need to look up a zip code if it hasn't been used previously.


Might not be elegant, just trying to think outside the box.

Thanks Arnold,
I appreciate your response. Unfortunately that is not an option. I'm tasked with delivering a complete solution. I found a download at https://sourceforge.net/projects/zips/ with all zip codes that includes Town,Stare, ZIP and Lat/Lon. I'll try to make something out of that.
Thanks you
Bill

Gotcha. I find the project intriguing, I hope you'll update the thread with your solution once you find one... I like the idea of pinging a website for data from within excel. Seems like something that could be useful for many applications. Best of luck!

Keep in mind, if you're calculating the miles between two points using longitude and latitude, you're getting straight line, "as the crow flies" distances, and not actual driving distances. The farther away from your shop the destination is, the less accurate your distance is likely to be. You might want to compare your results on a couple test zips against a service like Google Maps or MapQuest, to see if the difference is more than negligible... it may be a non-issue, but it's worth checking.

Arnold,
Thanks for the thought. I am actually in the process of sampling the data and checking to see what percentage of the distances are off. The furthest distance will be 300 miles. I may end up putting a percentage adder in the formula if my results show a need. This doesn't need to be .001 accurate, just within 5-10 miles

Thanks!
Bill

If you're only dealing with a range of 300 miles, I doubt it will be a big enough issue to worry about. My company is located on the east coast, and ships product all over the country, and sometimes beyond the borders... so highway routing would play into my calculations far more significantly, as some routes require travel through states that wouldn't even be touched by a point-to-point line. I was thinking on my terms, not yours, LOL! Glad you got it worked out!