Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Web Query for Java Applet driven data website | Excel Discussion (Misc queries) | |||
using excel to query asp page | Excel Programming | |||
Cant query a web page | Excel Programming | |||
page setup query | Excel Discussion (Misc queries) | |||
Web query & Passwords & Java & HTML | Excel Programming |