Hi. I'm a newbie on this forum so apologies in advance If I get things a bit wrong. I have a function that uses bing maps to calculate the distance and travel time between two points defined and returns the results to a cell in excel provided I have the longitude and latitude as the input to the function. I am now trying to write a further function to return the longitude and latitude from a postal (zip) code but I'm not sure of the output parameters I need to specify.
I'm using MS 365 Apps for Business and Excel version 2202. My function for the distance is as follows and it seems to work fine:
Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String, key As String
firstVal = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
secondVal = "&destinations="
key = "xxxxxxxx" (I have hashed this out)
lastVal = "&travelMode=driving&o=xml&key=" & key & "&distanceUnit=mi"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = firstVal & start & secondVal & dest & lastVal
objHTTP.Open "GET", Url, False
objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
GetDistance = Round(Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDistance"), 3), 1)
End Function
and the new function I am trying to write is:
Public Function GetLonglat(postcode As String)
Dim firstVal As String, secondVal As String, lastVal As String, key As String
firstVal = "https://dev.virtualearth.net/REST/v1/Locations/UK/"
secondVal = "&postalCode="
key = "xxxxxxxx" (I have hashed this out)
lastVal = "&o=xml&key=" & key
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = firstVal & secondVal & postcode & lastVal
objHTTP.Open "GET", Url, False
objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
GetLonglat = WorksheetFunction.FilterXML(objHTTP.responseText, "//location")
End Function
(I have also tried "//latitude" and "//longitude" for my output parameters as well and they don't work either. )
Can anyone tell me what I'm doing wrong please?
Thanks
CJ
