Find distance between two postcodes

A

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
 
J

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..
 
A

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
 
J

joel

I did something similar Using google the other day when you posted th
code. The code you provide was using AS THE CROW FLY I changed it t
Driving distance and added the required loop. I also made some change
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 Su
 
A

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 ?
 
J

joel

I was following your original code. The start locations was in D6 an
end Location in E6. Then each start and end location where two column
to the right. Each results was put in E7 and moved to the right by tw
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 th
correct boexes? I want to find out if the problem with the input dat
or the output data
 
A

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)
 
C

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


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
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..
Click to expand...
On Monday, November 02, 2009 9:37 AM joel wrote:
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;
Click to expand...
Click to expand...
Click to expand...
 
R

ron

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 inf6.

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 10:28 AM Adrian wrote:
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
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 towork
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
:
On Thursday, October 29, 2009 1:24 PM joel wrote:
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 itto
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
--
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 thecode 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 atrow 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
:
On Monday, November 02, 2009 9:37 AM joel wrote:
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 rightby 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">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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Distance between latitude and longitude 1
Determine the distance between 2 cells in a column 0
Plotting time and distance vs moon phase... 1
distance between UK postcodes for a find my nearest... 6
sub for calculating distance via post code / variable range. 7
Calculate distance between two points (numbers) in a number line 5
Calculate the difference between two row locations? 1
dictionary count is greater than number of keys? 3

Top