ExcelBanter - Request for Postal Codes

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Request for Postal Codes (https://www.excelbanter.com/excel-programming/430390-request-postal-codes.html)

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

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

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

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

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

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