Joel,
Here is the sample code that I was lookng at, can this be modified to work
down a sheet where the starting postcodes are in column A and the finish
postcodes are in column B placing the mileage result in column C
Private Sub CommandButton1_Click()
counter = 6
beginrange = Worksheets("sheet1").Cells(counter, 4).Address
endrange = Worksheets("sheet1").Cells(counter, 14).Address
For Each c In Worksheets("Sheet1").Range(beginrange, endrange).Cells
If c.Offset(0, 1).Value = "" Then counter = counter + 2
If counter = 20 Then Exit Sub
beginrange = Worksheets("sheet1").Cells(counter, 4).Address
endrange = Worksheets("sheet1").Cells(counter, 14).Address
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
DoEvents
Loop
Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)
Set Postcodebox = inputform.Item(0)
Postcodebox.Value = c.Value
Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = c.Offset(0, 1).Value
Set POSTCODEbutton = inputform.Item(2)
POSTCODEbutton.Click
Do While IE.busy = True
Loop
Set Table = IE.document.getElementsByTagname("Table")
Set DistanceTable = Table.Item(3)
Set DistanceRow = DistanceTable.Rows(2)
distance = Val(Trim(DistanceRow.Cells(2).innertext))
c.Offset(1, 1).Value = distance
IE.Quit
Next
End Sub
So the sheet would look like this before the macro ran and would have
mileages in column C when finished.
A B C D
1 CF83 4ES CM2 5PX
2 GL3 4PU DN21 1LG
3
4
--
Adrian
"joel" wrote:
Are you using any website. Can you provide a sample of the 2 postcodes
you are using for an example. I'm in the US and don;t the code and
would have to do a little research.
What can be done is using google get the map of the distance between
the two locations and extract the distance from the returned webpage..
--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148279
.