Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for Postal Codes
This request disappeared from the postings. do'nt know why. Here is the solution Sub Private Commandbutton1_Click() Postcode = InputBox("Enter PostCode: ") Postcode2 = InputBox("Enter 2nd PostCode: ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = _ "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp" IE.Navigate2 URL Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set MyForm = IE.document.getElementsByTagname("Form") Set inputform = MyForm.Item(0) Set Postcodebox = inputform.Item(0) Postcodebox.Value = Postcode Set Postcodebox2 = inputform.Item(1) Postcodebox2.Value = Postcode2 Set POSTCODEbutton = inputform.Item(2) POSTCODEbutton.Click Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Set DistanceTable = Table.Item(3) Set DistanceRow = DistanceTable.Rows(2) distance = Val(Trim(DistanceRow.Cells(2).innertext)) With Worksheets("sheet1") .Range("A1").Value = distance End With IE.Quit End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for Postal Codes
Hi Joel, This is the real answer to my question posted some days ago. Maybe you remember, you sent a sub to me, but that didn't cover exactly my question. Thanks, Stefi €˛Joel€¯ ezt Ć*rta: This request disappeared from the postings. do'nt know why. Here is the solution Sub Private Commandbutton1_Click() Postcode = InputBox("Enter PostCode: ") Postcode2 = InputBox("Enter 2nd PostCode: ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = _ "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp" IE.Navigate2 URL Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set MyForm = IE.document.getElementsByTagname("Form") Set inputform = MyForm.Item(0) Set Postcodebox = inputform.Item(0) Postcodebox.Value = Postcode Set Postcodebox2 = inputform.Item(1) Postcodebox2.Value = Postcode2 Set POSTCODEbutton = inputform.Item(2) POSTCODEbutton.Click Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Set DistanceTable = Table.Item(3) Set DistanceRow = DistanceTable.Rows(2) distance = Val(Trim(DistanceRow.Cells(2).innertext)) With Worksheets("sheet1") .Range("A1").Value = distance End With IE.Quit End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for Postal Codes
If you told me is was for postal codes I would of givin you my US zipcode macro which has a userform like the postal code. Enter 10001 to get NYC main post office. 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 Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "Stefi" wrote: Hi Joel, This is the real answer to my question posted some days ago. Maybe you remember, you sent a sub to me, but that didn't cover exactly my question. Thanks, Stefi €˛Joel€¯ ezt Ć*rta: This request disappeared from the postings. do'nt know why. Here is the solution Sub Private Commandbutton1_Click() Postcode = InputBox("Enter PostCode: ") Postcode2 = InputBox("Enter 2nd PostCode: ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = _ "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp" IE.Navigate2 URL Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set MyForm = IE.document.getElementsByTagname("Form") Set inputform = MyForm.Item(0) Set Postcodebox = inputform.Item(0) Postcodebox.Value = Postcode Set Postcodebox2 = inputform.Item(1) Postcodebox2.Value = Postcode2 Set POSTCODEbutton = inputform.Item(2) POSTCODEbutton.Click Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Set DistanceTable = Table.Item(3) Set DistanceRow = DistanceTable.Rows(2) distance = Val(Trim(DistanceRow.Cells(2).innertext)) With Worksheets("sheet1") .Range("A1").Value = distance End With IE.Quit End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for Postal Codes
Thanks, Joel, I was not after postal codes in particular but the searching technique in general. I learned a lot from both of your macros. Regards, Stefi €˛Joel€¯ ezt Ć*rta: If you told me is was for postal codes I would of givin you my US zipcode macro which has a userform like the postal code. Enter 10001 to get NYC main post office. 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 Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "Stefi" wrote: Hi Joel, This is the real answer to my question posted some days ago. Maybe you remember, you sent a sub to me, but that didn't cover exactly my question. Thanks, Stefi €˛Joel€¯ ezt Ć*rta: This request disappeared from the postings. do'nt know why. Here is the solution Sub Private Commandbutton1_Click() Postcode = InputBox("Enter PostCode: ") Postcode2 = InputBox("Enter 2nd PostCode: ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = _ "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp" IE.Navigate2 URL Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set MyForm = IE.document.getElementsByTagname("Form") Set inputform = MyForm.Item(0) Set Postcodebox = inputform.Item(0) Postcodebox.Value = Postcode Set Postcodebox2 = inputform.Item(1) Postcodebox2.Value = Postcode2 Set POSTCODEbutton = inputform.Item(2) POSTCODEbutton.Click Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Set DistanceTable = Table.Item(3) Set DistanceRow = DistanceTable.Rows(2) distance = Val(Trim(DistanceRow.Cells(2).innertext)) With Worksheets("sheet1") .Range("A1").Value = distance End With IE.Quit End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for Postal Codes
In message of Fri,
26 Jun 2009 05:20:01 in microsoft.public.excel.programming, Stefi writes Thanks, Joel, I was not after postal codes in particular but the searching technique in general. I learned a lot from both of your macros. Regards, Stefi [snip] Me too! I used some of Joel's code in another thread to write something to open a URL and extract some data. I hope to use the code in an earlier posting in this thread to drive the HTML as if by a human. What techniques do you (Joel) use to analyse an HTML page? This thread is disconnected from its origin. I have failed to find that in Google groups. It would help to put Joel's posting in context. -- Walter Briscoe |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Request for Postal Codes
Hi Joel
Was wondering, how do you know how to refer to any particular item on a web site in VBA? In the example you gave in response to my question how do you know that the output data is embeded in an object with tag name "table" or that the input data is under tag name "form" etc! I ask because I am looking at how I may do similar things with other web sites eg how I might use a site like multi map to achieve the same results! In Multi map the data goes into two input boxes but these do not appear to be referred to as "form" in this example. thanks John "Joel" wrote: If you told me is was for postal codes I would of givin you my US zipcode macro which has a userform like the postal code. Enter 10001 to get NYC main post office. 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 Form(0).submit Do While IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") If Table(0).Rows(0).innertext = "" Then MsgBox ("Invalid Zip code") Else Location = Table(0).Rows(2).innertext End If IE.Quit MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location) End Sub "Stefi" wrote: Hi Joel, This is the real answer to my question posted some days ago. Maybe you remember, you sent a sub to me, but that didn't cover exactly my question. Thanks, Stefi €˛Joel€¯ ezt Ć*rta: This request disappeared from the postings. do'nt know why. Here is the solution Sub Private Commandbutton1_Click() Postcode = InputBox("Enter PostCode: ") Postcode2 = InputBox("Enter 2nd PostCode: ") Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = _ "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp" IE.Navigate2 URL Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set MyForm = IE.document.getElementsByTagname("Form") Set inputform = MyForm.Item(0) Set Postcodebox = inputform.Item(0) Postcodebox.Value = Postcode Set Postcodebox2 = inputform.Item(1) Postcodebox2.Value = Postcode2 Set POSTCODEbutton = inputform.Item(2) POSTCODEbutton.Click Do While IE.readyState < 4 Or IE.busy = True DoEvents Loop Set Table = IE.document.getElementsByTagname("Table") Set DistanceTable = Table.Item(3) Set DistanceRow = DistanceTable.Rows(2) distance = Val(Trim(DistanceRow.Cells(2).innertext)) With Worksheets("sheet1") .Range("A1").Value = distance End With IE.Quit End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Postal codes | Excel Discussion (Misc queries) | |||
international postal codes | Excel Worksheet Functions | |||
formula for Canadian Postal Codes | Excel Worksheet Functions | |||
UK Postal codes in Excel | Excel Worksheet Functions | |||
Distances between Postal Codes | Excel Discussion (Misc queries) |