Getting longitude and latitude from Bing Maps API in Excel

Occasional Visitor

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

0 Replies