I'm trying to get the address in
https://dev.virtualearth.net/REST/v1/Locations/40.6718266667,-73.7601944444?o=xml&key=AqF-lvBxcTAEbhY5v0MfOHxhplD5NyaznesQ1IA5KS_RNghU1zrDiYN704mlrc8A
That's the ("//Location/Name")
The code is :
Function FindALocationByPoint(Lat As String, Lon As String, BingMapsKey As String) As String
Dim myRequest As XMLHTTP60
Dim uu As String
uu = "https://dev.virtualearth.net/REST/v1/Locations/" & Lat & "," & Lon & "?o=xml&key=" & BingMapsKey
Set myRequest = New XMLHTTP60
myRequest.Open "POST", uu, 0
myRequest.send
FindALocationByPoint = myRequest.readyState
(I know the final line should be FindALocationByPoint = myRequest.responseXML.SelectNodes("//Location/Name").Item(0).Text
) That will also return #VALUE!
I think the main problem is the unsuccessful connection to the website.
Then the cell=FindALocationByPoint(K2,L2,$W$4)
will return#VALUE!
If I delete myRequest.send
then the cell will return 1, which means server connection established, right?
Then, why adding myRequest.send
will return #VALUE!
?
Any Guidance?
THANK YOU SO MUCH. I've working with this for two days.
If I change the URL and set uu
equals another Geocoding website, there is no problem.
So is there something wrong with the website?(Microsoft Bing)
But I must use Bing, how to deal with this?
Thanks,
Ajax is not the problem here. You can load and use the long path to access:
Option Explicit
Public Sub test()
Const URL As String = "https://dev.virtualearth.net/REST/v1/Locations/40.6718266667,-73.7601944444?o=xml&key=AqF-lvBxcTAEbhY5v0MfOHxhplD5NyaznesQ1IA5KS_RNghU1zrDiYN704mlrc8A"
Dim sResponse As String, xmlDoc As Object 'MSXML2.DOMDocument60
With CreateObject("MSXML2.ServerXMLHTTP")
.Open "GET", URL, False
.send
sResponse = .responseText
End With
Set xmlDoc = CreateObject("MSXML2.DOMDocument") 'New MSXML2.DOMDocument60
With xmlDoc
.validateOnParse = True
.setProperty "SelectionLanguage", "XPath"
.async = False
If Not .LoadXML(sResponse) Then
Err.Raise .parseError.ErrorCode, , .parseError.reason
End If
Dim a As IXMLDOMElement
Set a = .LastChild.LastChild.FirstChild.LastChild.FirstChild.FirstChild
Debug.Print a.nodeTypedValue
End With
End Sub
If you execute the following script, it wll print you the same addresse twice dug out from different nodes. Let me know if this is what you expected or I got you wrong.
Sub GetAddress()
Const URL$ = "https://dev.virtualearth.net/REST/v1/Locations/40.6718266667,-73.7601944444?o=xml&key=AqF-lvBxcTAEbhY5v0MfOHxhplD5NyaznesQ1IA5KS_RNghU1zrDiYN704mlrc8A"
Dim xmlDoc As Object, elem$, elemAno$
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", URL, False
.send
Set xmlDoc = CreateObject("MSXML2.DOMDocument")
xmlDoc.LoadXML .responseXML.XML
End With
elem = xmlDoc.SelectNodes("//Location/Name")(0).Text
elemAno = xmlDoc.SelectNodes("//Address/FormattedAddress")(0).Text
Debug.Print elem, elemAno
End Sub