Distance between ZIP codes - Google Maps

[JMH022]

I searched the site and found some posts regarding the use of Google Maps through Excel, but none of them are quite what I need to do. Can anyone help?

I have a list of zip codes in Column A and Column B of my worksheet. I need to plug each of these zip codes into google maps to calculate the distance and return the value to the adjacent cell in Column C. Then I need it to loop: move to the next row and do it again until the list is complete.

Can anyone provide me with some VBA to accomplish this?

Appreciatively,

 

[bcmacuser]

Hi, I feel like I should try and help someone since I just posted my own question. Unfortunately, I don't have the time to write the code to send you an answer but maybe I'll be able to give you some ideas on how you can figure it out.

I work using geomaps software like manifold quite often for so I have an idea of what you want to do. My first thought is try to find a database or an excel sheet that has all the zip codes and the Lat and Long of those in another column. The US Census website must have something like this http://www.census.gov/epcd/www/zipstats.html for free. You could use this to do a vlookup and find the difference using a formula to find the difference in miles from one lat/long to another.

I know this doesn't directly answer you question but maybe it will give you an idea of how to think about it differently and help you solve your problem. As a side note, VB is pretty powerful so it also might be possible to link up to google maps.

Hope this helped....

 

[alansidman]

Is this thread helpful:
http://www.mrexcel.com/forum/showthread.php?t=448595&highlight=distance+zip+codes

Alan

 

[alansidman]

bcmacuser's suggestion reminded me that I had a small Access db with the information he suggested you use. You can export the table holding the data to excel. Here is a link to the database. I will remove it from this site in approximately 7 days.

http://www.box.net/shared/fsmtfah11x

Alan

 

[bcmacuser]

Thanks Alan! I downloaded for use myself

 

[alansidman]

One step further. This whole exercise got me intrigued. Here is a posting from Chip Pearson. He has the excel formulae already completed including VBA.

http://www.cpearson.com/excel/LatLong.aspx

Alan

 

[JMH022]

Thank you so much for your help on this. I will be looking over the materials you posted.

So am I understanding you right?...the best (only?) way to calculate distance is by converting the ZIP code to Lat/Long?

 

[bcmacuser]

I wouldn't as go as far to say that it's the best or the only way to do it....

However, this will definitely be fast and will use a minimal amount of coding. By connecting to GoogleEarth it requires open recordsets and calling for each field and a fairly advanced knowledge of VB. The solution Alan posted will give you a quick and easy way to efficiently find the distance between the two with a minimal amount of work. Hope this helps.

 

[JMH022]

understood. I will definitely explore adapting this to my project.

Alan: What is your source data for your ZIP code db? I am just curious to know what you used to generate Lat/Lon for each ZIP.

I'm getting better at VBA every time I use it, and I would still like to learn how to automate this type of search through a webpage like google maps. Afterall, if you're not learning in this world, why bother?

 

[alansidman]

Jim;
I am sorry, but I don't recall where I got the information. I probably downloaded the information from an Access Forum similar to Mr. Excel. I check the properties and it appears to have a data around September 2010.