VBA to get distance between two Postcodes using Bing API giving 400 error on certain lines.

[SimonPostcode]

This is the code

Option Explicit

Const BaseURL As String = "http://dev.virtualearth.net/REST/V1/Routes/Driving?wp.0="
Const APIKey As String = "tsNlc2fsKN0BN5fcfXdU~UEPmyF6E7-PVJQzuaM_o5w~Am8-J3IwQG7V_ZGHT02VjmfpEq7jx697-0UlLiqzDrIiMLaK5d3UIR24RpirTGyW"
Private Const READYSTATE_COMPLETE As Long = 4

Public Sub GetDistances(Addresses As Range)
Dim Server As Object
Dim ServerItem As Variant
Dim Servers As Object
Dim Cell As Range
Dim URL As String

Set Servers = CreateObject("Scripting.Dictionary")

'Send all the requests up front, but don't wait for them to complete
For Each Cell In Addresses
'See here: Driving Route Example - Bing Maps for more details on this api
URL = BaseURL & Cell & "&wp.1=" & Cell.Offset(0, 1) & "&key=" & APIKey & "&DistanceUnit=mi&DurationUnit=min&output=xml"
Set Server = CreateObject("MSXML2.ServerXMLHTTP")
Server.Open "GET", URL, True 'Last param will make request async
Server.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
Server.send ("")
Servers.Add Cell.Address, Server
Next

'Iterate each XML request sent to see if done
For Each ServerItem In Servers.Keys()
Set Server = Servers(ServerItem)

While Server.readyState <> READYSTATE_COMPLETE
DoEvents
Wend

'Parse result
If Server.Status = 200 Then
'Add result to the sheet to an offsetting column
Addresses.Parent.Range(ServerItem).Offset(0, 2) = WorksheetFunction.FilterXML(Server.ResponseText, "/Response/ResourceSets/ResourceSet/Resources/Route/TravelDistance")
Addresses.Parent.Range(ServerItem).Offset(0, 3) = WorksheetFunction.FilterXML(Server.ResponseText, "/Response/ResourceSets/ResourceSet/Resources/Route/TravelDuration") / 60
'You can also return the lat/long from this request, see --> Driving Route Example - Bing Maps
End If
Next

End Sub

Sub ProcessData()
Dim myRng As Range
Dim t As Double
t = Timer
Set myRng = ThisWorkbook.Sheets(1).Range("A1:a20")
GetDistances myRng
Debug.Print Timer - t
End Sub

The code does pull through the distance on some lines but others give an error

First line pulls both distance in miles and time in minutes with no issue but the second just gives an error.

Any help would be apperciated.

Thanks.

 

[John_w]

First line pulls both distance in miles and time in minutes with no issue but the second just gives an error.

Does it work with "BD21 4TP"?