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

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?


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


where <zipcode> is the actual zip code.

The Lat is at xpath


and the long is at


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

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.
Scroll less than a 1/4 of the way down the page until you see the Title: Get Latitude and Longitude using XML

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:

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.