Web Query (Java Page)

  • Thread starter Thread starter Ron Rosenfeld
  • Start date Start date
R

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
 
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
 
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
Click to expand...


Thank you very much, Joel.

I will give that a try.

--ron
 
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.
Click to expand...

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
 
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:
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top