VBA Macro to Find Distance between Two UK Postcodes

[TM123]

I have tried running the macro made by hoppy69efc in Dec. 2011 to use Google Maps to calculate the distance between two postcodes. My macro reads the postcodes in cols. D & E and puts the distance in Col. F.
When I run the macro in Excel 2003 and XP, it gets to the line calling the function getGoogDistanceTime and produces a Runtime Error 424 - Object Required. Can anyone tell me what is wrong?

Below is the macro:

Sub Postcoder()
Dim nSelRow, nRowNo, nLastRow, nDist As Integer
Dim rngStart, rngEnd, rSel As Range

nRowNo = 1
While Range("A" & nRowNo) <> ""
nRowNo = nRowNo + 1
Wend
nLastRow = nRowNo - 1

Set rSel = ActiveWindow.RangeSelection
nSelRow = rSel.Row
If (nSelRow = 1 Or nSelRow > nLastRow) Then
MsgBox "Selected Row " & nSelRow & " is out of range"
GoTo lWrongRow
End If
While nSelRow < nLastRow
Range("F" & nSelRow).Value = getGoogDistanceTime(Range("E" & nSelRow).Value, Range("D" & nSelRow).Value, "distance")
nSelRow = nSelRow + 1
Wend

lWrongRow:
End Sub
Public Function getGoogDistanceTime(rngSAdd As Range, rngEAdd As Range, Optional strReturn As String = "distance") As Variant
Dim sURL As String
Dim BodyTxt As String
Dim vUnits As Variant
Dim dblTemp As Double
Dim bUnit As Byte
sURL = "Google Maps"
sURL = sURL & "&saddr=" & Replace(rngSAdd(1).Value, " ", "+")
sURL = sURL & "&daddr=" & Replace(rngEAdd(1).Value, " ", "+")
sURL = sURL & "&hl=en"
BodyTxt = getHTML(sURL)
If InStr(1, BodyTxt, strReturn, vbTextCompare) = 0 Then
getGoogDistanceTime = "Error"
Else
getGoogDistanceTime = parseGoog(strReturn, BodyTxt)
If LCase(strReturn) Like "time*" Then
vUnits = Split(getGoogDistanceTime)
For bUnit = LBound(vUnits) To UBound(vUnits) - 1 Step 2
dblTemp = dblTemp + _
Val(vUnits(bUnit)) / Choose(InStr(1, "hms", Left(vUnits(bUnit + 1), 1), vbTextCompare), 24, 1440, 86400)
Next bUnit
getGoogDistanceTime = dblTemp
Else
getGoogDistanceTime = Val(getGoogDistanceTime)
End If
End If
End Function
Public Function getHTML(strURL As String) As String
Dim oXH As Object
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", strURL, False
.send
getHTML = .responseText
End With
Set oXH = Nothing
End Function
Public Function parseGoog(strSearch As String, strHTML As String) As String
'strSearch = strSearch & ":"""
strSearch = strSearch & ":'"
If InStr(1, strHTML, strSearch) = 0 Then
parseGoog = "Not Found"
Exit Function
Else
parseGoog = Mid(strHTML, InStr(1, strHTML, strSearch) + Len(strSearch))
parseGoog = Mid(parseGoog, 1, InStr(1, parseGoog, """") - 1)
End If
End Function

 

[hiker95]

TM123,

Welcome to the MrExcel forum.

Maybe something below will assist you:

Finding the location of a non-US city
Here are some sites that have the locations of major cities in the world.
Latitude-Longitude of World Cities ([url]www.realestate3d.com)[/url]
Latitude and Longitude of World Cities (London, L.A., Paris, Singapore) — Infoplease.com

Try this udf it will help you in finding the time and distance between two cities using Google Map
Ashish Koul
browse("excel macros", "excel tips")
browse("access macros", "access tips")

 

[TM123]

Thanks, but I really need to work out distances within my home city. I do voluntary work visiting people needing fixes to their hearing aids and can claim mileage, but it is a tedious task looking up the distances between each visit using Google Maps or similar tools.