Using Excel to Fill Zip Code Data from Geocoder

Hello again,

I've got roughly 12,000 address without zip codes and I need to lookup all of these codes manually to get the job done. I don't want to waste Thousands of hours to do it either.

I have Address, City, and State. I just need Zip.

Doing this manually from http://zip4.usps.com/zip4/welcome.jsp takes hours.

I found this write up, but it doesn't work from Excel, because it's for Google Docs.

Hard to do in Excel. Would need to call a Web Service using SOAP toolkit, or use Visual Studio. One must wonder what MS is thinking...

Much easier to do in Google Docs Spreadsheet, then export to Excel:

can use GeoCoder.ca's XML web service to look up the zip code by address. In Google docs, you would use this function:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//postal")

where A2 is the street address

you can also get the latitude and longitude this way:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//geodata")

Please note that GeoCoder has a throttle on requests per day for their free service.

Originally Posted by shg

See attached. Note that it requires the following references:

Microsoft VBScript Regular Expressions
Microsoft WinHTTP Services

OMFG! That's awesome! Thanks. That's definitely a keeper file :D

I’ve developed an Excel addin that does forward/reverse gecoding, with GoogleMaps in a task-pane and GCD calculations.
Freeware: http://www.calvert.ch/geodesix/

what do i have to do to enable this sheet? Every time i put in an address I get this: "backend fetch error 404". Please help.

Re: Using Excel to Fill Zip Code Data from Geocoder

See attached. Note that it requires the following references:

Microsoft VBScript Regular Expressions
Microsoft WinHTTP Services

kthakur ... I believe the problem to be http://local.yahooapis.com/MapsServi...deResponse.xsd

After obtaining my own ID and editing the VBA code ... I saw the above line of code and recalled that Yahoo shut it down.

When I click the GeoCode button I get the following error:

Gecoder - runtime error!

Error - 2147012889 : The server name or address could not be resolved

Hope this helps ... have you found another solution yet?

I'll post back here if I figure out a fix.

Joe

How do I reference the following?

Microsoft VBScript Regular Expressions
Microsoft WinHTTP Services

Originally Posted by shg

See attached. Note that it requires the following references:

Microsoft VBScript Regular Expressions
Microsoft WinHTTP Services

sorry to bump this up. when i downloaded and open up the file,all i cld see was excelforum main page.
And i'm nuts on this coding except copy & paste and edit to make it work. hope someone could help me

Hi I see this error message. Can one of you please help me out of it.

Geocoder-runtime error!
Error-2147012889: The server name or address could not be resolved