Zip Code

[cbrsix]

I was wondering if it is possible to create a lookup field that grabs information from the internet (similar to a web query in MS excel). I have a table with client addresses and I want to be able to type in the zip code and have it automatically fill in the city and state by looking up that particular zip code from online somehow. Is this possible?

Thanks.

[June7]

Grabbing data from a web page is possible but not easy. I have seen threads on the topic but never implemented anything. You can buy zip code datasets.

[cbrsix]

Would you recommend one over the other as a better solution?

[June7]

I think the web grab will be very difficult, if even possible. For the other option, look at this http://databases.about.com/od/access...dedatabase.htm

[orange]

I haven't tried working with a web page to submit a value and try to parse the returned html for a response.
However, here is a site, and there is a box for you to enter a ZIP code,
http://www.50states.com/zipcodes/alabama.htm
It will return a response.
http://www.411.com/search/ReverseZip...0744&zip=33468

So you would have some vba to submit your zip code to that site, and some vba to get the returned html and parse through it to find the City and State.

If you can get something working, I'm sure others would be interested.

Good luck.

[June7]

I can get you this far. I found USPS web page that when it opens cursor is in an entry box. Unfortunately, can't get it to execute the search. User has to click button on web page. Set a VBA library reference to Microsoft Internet Controls.

Private Sub tbxZip_AfterUpdate()
Dim oBrowser As InternetExplorer
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.Navigate "https://tools.usps.com/go/ZipLookupAction!input.action"
oBrowser.Visible = True
Do
    'Wait till the Browser is loaded
Loop Until oBrowser.ReadyState = READYSTATE_COMPLETE
SendKeys "{tab}{tab}{tab}{tab}" & Me.tbxZip & "{tab}", True
End Sub

Even if could force the search, no idea how to parse the data from the results page. Then there is the issue of closing the page. I don't think VBA can selectively close a web page, have to kill all instances of the browser. I also can't get code to work with any other browser.

As I said earlier, not easy, if at all.

[alansidman]

I just found an up to date site for Zip codes. The file can be downloaded as a .csv and then imported.

http://federalgovernmentzipcodes.us/

Alan

[cbrsix]

Thanks for the suggestions. I think I'm just going to import a data set with all of the needed data.