Web Query (Java Page)
 

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

Web Query (Java Page)
 

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:

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

Web Query (Java Page)
 

On Mon, 3 Nov 2008 19:12:01 -0800, Joel 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




Thank you very much, Joel.

I will give that a try.

--ron

Web Query (Java Page)
 

On Mon, 3 Nov 2008 19:12:01 -0800, Joel 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.


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

Web Query (Java Page)
 

I made a small change to get the invalid zip codes

"Ron Rosenfeld" wrote:

On Mon, 3 Nov 2008 19:12:01 -0800, Joel 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.


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

Inverse result
 

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?

On Monday, November 03, 2008 4:01 PM Ron Rosenfeld wrote:

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:


On Tuesday, November 04, 2008 6:37 AM Ron Rosenfeld wrote:

Thank you very much, Joel.

I will give that a try.

--ron


On Tuesday, November 04, 2008 8:04 PM Ron Rosenfeld wrote:

On Mon, 3 Nov 2008 19:12:01 -0800, Joel wrote:


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


On Wednesday, November 05, 2008 9:56 AM Joe wrote:

I made a small change to get the invalid zip codes

"Ron Rosenfeld" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: Excel 2010 - The Missing Manual [OReilly]
http://www.eggheadcafe.com/tutorials...l-oreilly.aspx