Get latitude and longitude from Bing using VBA

[Aleksandr678]

I use this code to get latitude and longitude from Bing to a cell. I have address and Bing returns me coordinates.


Function GeocodeAddress(address As String, BingMapsKey As String) As String
Dim oHttpReq As MSXML2.XMLHTTP60
Set oHttpReq = New MSXML2.XMLHTTP60
oHttpReq.Open "get", "https://dev.virtualearth.net/REST/v1/Locations?q=" & address & "&o=xml&key=" & BingMapsKey, "false"
oHttpReq.send
If oHttpReq.readyState = 4 Then
GeocodeAddress = oHttpReq.responseXML.SelectNodes("//Point/Latitude").Item(0).Text & "," & oHttpReq.responseXML.SelectNodes("//Point/Longitude").Item(0).Text
End If
End Function


But formula gives me Error

If I use manually url I receive that kind of XML-answer:





-<Response >

<Copyright></Copyright>
<BrandLogoUri></BrandLogoUri>
<StatusCode>200</StatusCode>
<StatusDescription>OK</StatusDescription>
<AuthenticationResultCode>ValidCredentials</AuthenticationResultCode>
<TraceId>befd656f59594c84bb7ceefce9c9d00b|CH10043736|02.00.131.2700|CH1SCH050021343</TraceId>
-<ResourceSets>
-<ResourceSet>
<EstimatedTotal>1</EstimatedTotal>
-<Resources>
-<Location>
<Name>Canada</Name>
-<Point>
<Latitude>62.832908630371094</Latitude>
<Longitude>-95.913322448730469</Longitude>
</Point>
+<BoundingBox>
<EntityType>CountryRegion</EntityType>
-<Address>
<CountryRegion>Canada</CountryRegion>
<FormattedAddress>Canada</FormattedAddress>
</Address>
<Confidence>High</Confidence>
<MatchCode>Good</MatchCode>
-<GeocodePoint>
<Latitude>62.832908630371094</Latitude>
<Longitude>-95.913322448730469</Longitude>
<CalculationMethod>Rooftop</CalculationMethod>
<UsageType>Display</UsageType>
</GeocodePoint>
</Location>
</Resources>
</ResourceSet>
</ResourceSets>
</Response>



I think that I miss smth in a path inside XML-answer and script can`t grab needed info

Please help me to find error

[p45cal]

Could we have an example calling statement to include what's in the variables address and BingMapsKey?

[Aleksandr678]

Address is "Canada" (it concerns the example of XML-answer) and BingMapsKey - is a huge amount letters and digits that Bing gives to control quires (it`s a public key that allows only 50000 answers in a day from Bing).

[p45cal]

BingMapsKey - is an huge amount letters and digits that Bing gives to control quires (it`s a public key that allows only 50000 answers in a day from Bing).So could you quote it here, or perhaps tell us how to get one? (Difficult to test without it).

[Aleksandr678]

BING KEY - Ahln7LUfvWy-CJ_LdkSCmBwV32u5Mcle13OKVpL3W0k3RCS6PeCo4mag9rH-eWYK

[Aleksandr678]

I wanted to post URL but I need to have 5 posts to be able to post Url inside. ^)

[p45cal]

Struggling with MSXML2.XMLHTTP60, but while I'm still struggling with it you can try:
Function GeocodeAddress2(address As String, BingMapsKey As String) As String
Dim myURL As String
myURL = "https://dev.virtualearth.net/REST/v1/Locations?q=" & address & "&o=xml&key=" & BingMapsKey
Set xmldoc = CreateObject("Microsoft.XMLDOM")
xmldoc.async = False
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", myURL, False
.send
xmldoc.LoadXML .responseText
End With
GeocodeAddress2 = xmldoc.SelectNodes("//Point/Latitude").Item(0).Text & "," & xmldoc.SelectNodes("//Point/Longitude").Item(0).Text
End Function
Note the 2 after the function name.
No need for any reference to be set as it uses late binding.
It worked here.

Also note that in your code I don't think there should be quote marks around false

[p45cal]

Struggle over:
Function GeocodeAddress4(address As String, BingMapsKey As String) As String
Dim oHttpReq As MSXML2.XMLHTTP60
Set oHttpReq = New MSXML2.XMLHTTP60
oHttpReq.Open "get", "https://dev.virtualearth.net/REST/v1/Locations?q=" & address & "&o=xml&key=" & BingMapsKey, False
oHttpReq.send
If oHttpReq.readyState = 4 Then
oHttpReq.responseXML.setProperty "SelectionNamespaces", "xmlns:xsi='http://schemas.microsoft.com/search/local/ws/rest/v1'"
GeocodeAddress4 = oHttpReq.responseXML.SelectNodes("//xsi:Latitude").Item(0).Text & "," & oHttpReq.responseXML.SelectNodes("//xsi:Longitude").Item(0).Text
End If
End Function
Note the 4 after the function name. You can change this to just
GeocodeAddress
as long as you also change:
GeocodeAddress4 = oHttpReq.responseXML…
to:
GeocodeAddress = oHttpReq.responseXML…
and don't have another function with the same name.

[p45cal]

I see that there are 2 sets of Lat/Lon in the xml returned:
-<Point>
<Latitude>62.832908630371094</Latitude>
<Longitude>-95.913322448730469</Longitude>
</Point>

and

-<GeocodePoint>
<Latitude>62.832908630371094</Latitude>
<Longitude>-95.913322448730469</Longitude>
<CalculationMethod>Rooftop</CalculationMethod>
<UsageType>Display</UsageType>
</GeocodePoint>

Of course in this instance they're the same, but might they be different in other cases? I don't know.

From the name of the function one might expect that you wanted the 2nd of these, whereas the code above returns the first of these.
If the Geocode lat/lon is required, change this line in the function:
GeocodeAddress4 = oHttpReq.responseXML.SelectNodes("//xsi:Latitude").Item(0).Text & "," & oHttpReq.responseXML.SelectNodes("//xsi:Longitude").Item(0).Text
to:
GeocodeAddress4 = oHttpReq.responseXML.SelectSingleNode("//xsi:GeocodePoint/xsi:Latitude").Text & "," & oHttpReq.responseXML.SelectSingleNode("//xsi:GeocodePoint/xsi:Longitude").Text

[Aleksandr678]

Thank you!

I tried all this two approaches but I received an error again. I`ve checked - URL is acceptable (I receive xml-answer) in browser. May be I need to turn on some references in VBA? I turned on Microsoft XML, v6.0 and have others turned on by default.

[Aleksandr678]

I wrote simple script basing on your GeocodeAddress4 code. It receives coordinate in macro and puts in into a cell (not using Function). And it works! Thank you again!

[p45cal]

an error again. I`ve checked - URL is acceptable (I receive xml-answer) in browser. May be I need to turn on some references in VBA? I turned on Microsoft XML, v6.0 and have others turned on by default.Yes, the GeoCodeAddress4 needs a reference to xml 6.0, but I had inferred you already had that as your first code snippet needs it.

I tried late binding with Set oHttpReq = CreateObject("MSXML2.XMLHTTP60") but my 32-bit machine didn't want to play.

Worksheet formulae work here with all codes I suggested.

Screenshots; first with formula showing:
15441



and then showing result:
15442

[p45cal]

I tried late binding with Set oHttpReq = CreateObject("MSXML2.XMLHTTP60") but my 32-bit machine didn't want to play.
Correction: With a bit more perseverance the following worked with no special xml references required:
Function GeocodeAddress4(address As String, BingMapsKey As String) As String
Dim oHttpReq As Object
'Set oHttpReq = CreateObject("MSXML2.XMLHTTP60")'failed
Set oHttpReq = CreateObject("MSXML2.XMLHTTP.6.0")
oHttpReq.Open "get", "https://dev.virtualearth.net/REST/v1/Locations?q=" & address & "&o=xml&key=" & BingMapsKey, False
oHttpReq.send
If oHttpReq.readyState = 4 Then
oHttpReq.responseXML.setProperty "SelectionNamespaces", "xmlns:xsi='http://schemas.microsoft.com/search/local/ws/rest/v1'"
GeocodeAddress4 = oHttpReq.responseXML.SelectSingleNode("//xsi:GeocodePoint/xsi:Latitude").Text & "," & oHttpReq.responseXML.SelectSingleNode("//xsi:GeocodePoint/xsi:Longitude").Text
End If
End Function

[Aleksandr678]

I take a look at your screenshots and found the mistake - I put BingKey inside formula. But since I`ve written BingKey into a cell and made a link in formula to that cell - it begins to work. Thank you again for the help!!!

[p45cal]

A formula such as:
=GeocodeAddress4(E5,"Ahln7LUfvWy-CJ_LdkSCmBwV32u5Mcle13OKVpL3W0k3RCS6PeCo4mag9rH-eWYK")
or
=GeocodeAddress4("Canada","Ahln7LUfvWy-CJ_LdkSCmBwV32u5Mcle13OKVpL3W0k3RCS6PeCo4mag9rH-eWYK")
should work.
Did you forget the quote marks?

[Aleksandr678]

Yes, I did. So it makes me to have some more practice in coding %)

[delkhanna]

A formula such as:
=GeocodeAddress4(E5,"Ahln7LUfvWy-CJ_LdkSCmBwV32u5Mcle13OKVpL3W0k3RCS6PeCo4mag9rH-eWYK")
or
=GeocodeAddress4("Canada","Ahln7LUfvWy-CJ_LdkSCmBwV32u5Mcle13OKVpL3W0k3RCS6PeCo4mag9rH-eWYK")
should work.
Did you forget the quote marks?