Web Query (Java Page)

[Ron Rosenfeld]

I would like to be able to do zip code lookups from within Excel 2007. I would
like to be able to input a list of zip codes into a column, and have Excel go
to the Internet to find matching cities.

A source for this information http://zip4.usps.com/zip4/citytown_zip.jsp but I
have not found documentation to set up an Excel Web Query to obtain information
from this sort of page.

Is it possible, and is there documentation on the Internet, for accomplishing
this?

Thanks.
--ron

 

[Joel]

You can use the Internet Explorer Application to get the City Name. I only
got the First City Name but this can be modified to get all the names.

Sub GetZipCodes()

ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE

Set ZipCodebutton = Form(0).onsubmit

Form(0).Submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub

 

[Ron Rosenfeld]

You can use the Internet Explorer Application to get the City Name. I only
got the First City Name but this can be modified to get all the names.

Sub GetZipCodes()

ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE

Set ZipCodebutton = Form(0).onsubmit

Form(0).Submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub

Thank you very much, Joel.

I will give that a try.

--ron

 

[Ron Rosenfeld]

You can use the Internet Explorer Application to get the City Name. I only
got the First City Name but this can be modified to get all the names.

Great, Joel. I've got it working selecting the zip codes from a list on a
worksheet, and writing the city name into the adjacent column.

Now all I need to do is work out the issue of invalid zip codes, and I'll be
set.

Thank you very much for your suggestions.
--ron

 

[Joel]

I made a small change to get the invalid zip codes

 

[Derek Myronuk]

Can this code be made to return the zip code from a row of three columns that provide the Address, City and State to return a zip code?

I would like to be able to do zip code lookups from within Excel 2007. I would
like to be able to input a list of zip codes into a column, and have Excel go
to the Internet to find matching cities.

A source for this information http://zip4.usps.com/zip4/citytown_zip.jsp but I
have not found documentation to set up an Excel Web Query to obtain information
from this sort of page.

Is it possible, and is there documentation on the Internet, for accomplishing
this?

Thanks.
--ron

On Monday, November 03, 2008 10:12 PM Joe wrote:
You can use the Internet Explorer Application to get the City Name. I only
got the First City Name but this can be modified to get all the names.

Sub GetZipCodes()

ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE

Set ZipCodebutton = Form(0).onsubmit

Form(0).Submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub


"Ron Rosenfeld" wrote:

Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: Excel 2010 - The Missing Manual [OReilly]
http://www.eggheadcafe.com/tutorial...w-excel-2010--the-missing-manual-oreilly.aspx