使用XMLHttpRequest的VBA代码始终返回#VALUE！在Excel中 [英] VBA code using XMLHttpRequest always returns #VALUE! in Excel

问题描述

我想在

https://dev.virtualearth.net/REST/v1/Locations/40.6718266667,-73.7601944444?o=xml&key=AqF-lvBxcTAEbhY5v0MfOHxhplD5NyaznesQ1IA5KS_RNghU1zrDiYN704mlrc8A

那是("//位置/名称")

That's the ("//Location/Name")

代码是:

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

(我知道最后一行应该是 FindALocationByPoint = myRequest.responseXML.SelectNodes("//Location/Name").Item(0).Text )这也将返回#值！我认为主要问题是与网站的连接失败.

(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.

然后单元格 = FindALocationByPoint(K2，L2，$ W $ 4)将返回 #VALUE！如果我删除 myRequest.send ，那么该单元将返回1，这意味着已建立服务器连接，对吧?

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?

然后，为什么添加 myRequest.send 会返回 #VALUE！?

Then, why adding myRequest.send will return #VALUE! ?

任何指导?

非常感谢.我已经为此工作了两天.如果我更改URL并将 uu 设置为等于另一个地理编码网站，则没有问题.那网站有问题吗(Microsoft Bing)但是我必须使用必应，该如何处理?

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?

谢谢

推荐答案

如果执行以下脚本，它将打印两次从不同节点挖出的相同地址.让我知道这是您所期望的还是我弄错了.

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

这篇关于使用XMLHttpRequest的VBA代码始终返回#VALUE！在Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！