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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.