Find distance between two postcodes

[Adrian]

I have just read a thread on how do do this but unfortunately it went right
over my head. I wonder if anyone could help with some code which just looked
at a postcodes in column A and B in a worksheet and recorded the road
distance between the two in column C looping until meeting empty cells ?

Adrian

[joel]

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/showthread.php?t=148279

[Adrian]

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:

> .
>

[joel]

I did something similar Using google the other day when you posted the
code. The code you provide was using AS THE CROW FLY I changed it to
Driving distance and added the required loop. I also made some changes
to make it easier to understand.

Private Sub CommandButton1_Click()

RowCount = 6
FirstCol = "D"
LastCol = "N"
ColCount = Columns(FirstCol).Column

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.postcode.org.uk/country/uk/" & _
"_postcode-distance-calculator.asp"

'get to first webpage
IE.Navigate2 URL
Do While IE.readyState <> 4 Or _
IE.busy = True

DoEvents
Loop

With Worksheets("Sheet1")
Do While .Cells(RowCount, ColCount) <> ""
StartLocation = .Cells(RowCount, ColCount).Value
EndLocation = .Cells(RowCount, ColCount + 1).Value

Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)

Set Postcodebox = inputform.Item(0)
Postcodebox.Value = StartLocation

Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = EndLocation

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(4).innertext))

.Cells(RowCount + 1, ColCount + 1) = distance

ColCount = ColCount + 2
Loop
End With

IE.Quit

End Sub

[Adrian]

Thanks Joel but maybe I am doing something wrong, I have copied the code into
a new module in a new book and placed the starting postcodes into column A
starting at row 6 and finishing postcodes into column B starting at row 6.
The macro runs and the website opens but it does not return the result in
cell C6 as expected. Have I done something wrong ?
--
Adrian

"joel" wrote:

> .
>

[joel]

I was following your original code. The start locations was in D6 and
end Location in E6. Then each start and end location where two columns
to the right. Each results was put in E7 and moved to the right by two
columns ] one row down and one row to the right .offset(1,1)].

Is the Start and End Locations being put in to the website in the
correct boexes? I want to find out if the problem with the input data
or the output data.

--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=148279

&lt;a href=&quot;http://www.thecodecage.com&quot;&gt;Microsoft Office Help&lt;/a&gt;

[ant_west]

Joel - thanks, that is brilliant and just what I needed!

I changed your macro slightly for my needs - in particular I changed
the line

ColCount = ColCount + 2

to

RowCount = RowCount + 1

as I was going down a column. (I was also calculating distances from
one fixed point)

[chris sibson]

Hi

I have just come across this conversation and i have a simular task i need to perform.

i have over 1000 postcodes i need to find out how far they are away from our office (an approx transport time)

I have copied and pasted the code into a new module setting it up as macro2. The code takes my start and finish postcodes, opens the website inputs the postcodes and gives the distance. it then returns an error. Can anyone help?

My workbook has starting post in d6 finish in e6 and i want the result in f6.

Really appreciate it if someone can.

Current code:

Sub Macro2()

RowCount = 6
FirstCol = "D"
LastCol = "N"
ColCount = Columns(FirstCol).Column

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.freemaptools.com/distance-between-uk-postcodes.htm"

'get to first webpage
IE.Navigate2 URL
Do While IE.readyState <> 4 Or _
IE.busy = True

DoEvents
Loop

With Worksheets("Sheet1")
Do While .Cells(RowCount, ColCount) <> ""
StartLocation = .Cells(RowCount, ColCount).Value
EndLocation = .Cells(RowCount, ColCount + 1).Value

Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)

Set Postcodebox = inputform.Item(0)
Postcodebox.Value = StartLocation

Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = EndLocation

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) ' this is where its going wrong.

distance = Val(Trim(DistanceRow.Cells(4).innertext))

Cells(RowCount + 1, ColCount + 1) = distance

RowCount = RowCount + 1
Loop
End With

IE.Quit

End Sub

>> On Tuesday, October 27, 2009 12:12 PM joel wrote:

>> Are you using any website. Can you provide a sample of the 2 postcodes

>> you are using for an example. I am 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/showthread.php?t=148279

>>> On Thursday, October 29, 2009 10:11 AM Adrian wrote:

>>> 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:

>>>> Cells(RowCount + 1, ColCount + 1) = distance
>>>>

>>>> ColCount = ColCount + 2

>>>> Loop
>>>> End With
>>>>
>>>>
>>>> IE.Quit
>>>>
>>>> End Sub
>>>>
>>>>

>>>> --
>>>> joel
>>>> ------------------------------------------------------------------------
>>>> joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
>>>> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=148279

>>>>> On Monday, November 02, 2009 9:25 AM Adrian wrote:

>>>>> Thanks Joel but maybe I am doing something wrong, I have copied the code into
>>>>> a new module in a new book and placed the starting postcodes into column A
>>>>> starting at row 6 and finishing postcodes into column B starting at row 6.
>>>>> The macro runs and the website opens but it does not return the result in
>>>>> cell C6 as expected. Have I done something wrong ?
>>>>> --
>>>>> Adrian
>>>>>
>>>>>
>>>>> "joel" wrote:

>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>> Dynamic Data Controls with Entity Framework
>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/29c02d78-c90d-495a-82fd-c21fe6656ed6/dynamic-data-controls-with-entity-framework.aspx

[ron]

On Nov 1, 9:32 am, chris sibson <christopher.sib...@cummins.com>
wrote:

> >>>>>> &lt;a href=&quot;http://www.thecodecage.com">MicrosoftOffice Help&lt;/a&gt;

> >>>>>>> On Wednesday, December 16, 2009 2:21 PM ant_west wrote:
> >>>>>>> Joel - thanks, that is brilliant and just what I needed!
>
> >>>>>>> I changed your macro slightly for my needs - in particular I changed
> >>>>>>> the line
>
> >>>>>>> ColCount = ColCount + 2
>
> >>>>>>> to
>
> >>>>>>> RowCount = RowCount + 1
>
> >>>>>>> as I was going down a column. (I was also calculating distances from
> >>>>>>> one fixed point)
> >>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
> >>>>>>> Dynamic Data Controls with Entity Framework

> >>>>>>>http://www.eggheadcafe.com/tutorials/aspnet/29c02d78-c90d-495a-82fd-c...- Hide quoted text -
>
> - Show quoted text -

Chris...Try replacing the code after the "POSTCODEbutton.Click" line
and before the "RowCount = RowCount + 1" line with the following the
following

distance = 0
Do Until distance > 0
my_code = ie.Document.body.innerhtml
pos_1 = InStr(1, my_code, "transport value", vbTextCompare)
pos_2 = InStr(pos_1, my_code, "=", vbTextCompare)
pos_3 = InStr(pos_2, my_code, "read", vbTextCompare)
distance = Val(Trim(Mid(my_code, 1 + pos_2, -1 + pos_3 - (1 +
pos_2))))
Loop

Cells(RowCount, ColCount + 2) = distance

...Ron