PDA

View Full Version : VBA for Latitude and Longitude from 5 digit Postal Code



bi2L'sy
06-16-2011, 12:33 PM
I want to key off of a zip code and get latitude and logitude without having to do it one by one. Are there any ways of using VBA to get the geocode? I would like to use google earth and an excel file to do some mapping. Any code out there that does this?

thanks!

Wireless Guy
06-17-2011, 07:56 AM
You can write code that pops it all into this URL

http://zipinfo.com/cgi-local/zipsrch.exe?ll=ll&zip=<zipcode>&Go=Go

where <zipcode> is the actual zip code.

The Lat is at xpath

/html/body/table/tbody/tr/td[2]/center[3]/table/tbody/tr[2]/td[4]

and the long is at

/html/body/table/tbody/tr/td[2]/center[3]/table/tbody/tr[2]/td[5]

but I don't know how to extract them out from there. if the XML had some id tags it'd be easier!

frank_m
06-17-2011, 11:21 AM
There is a vba function at the link below that looks as though it lets you pass the zip code parameter to calculate the longitude and latitude.
http://www.codeforexcelandoutlook.com/excel-vba/latitude-longitude-functions/
Scroll less than a 1/4 of the way down the page until you see the Title: Get Latitude and Longitude using XML

Edit:
After further reading the code instruction's, apparently the code will only run in Windows XP or 2000 as it requires a reference to "Microsoft XML, version 2.0" and is said to fail with updated versions found in Vista and Win 7
Brief discussion about it at this Ozgrid link, with no solution given:
http://www.ozgrid.com/forum/showthread.php?t=95115&page=1

Shred Dude
06-18-2011, 08:50 AM
I wrote code for the site you refer to but it rate limits the free look ups to 30 per day.

There is a download file available here: http://federalgovernmentzipcodes.us/
that will probably meet your needs as a source to do lookups from. The .xlsx file has 80265 rows, with lat/long and many other attributes per zip code.