ajax - 使用 XMLHttpRequest 的 VBA 代码总是返回 #VALUE!在 Excel 中

Question

我正在尝试获取地址

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

那是(“//位置/名称”)

代码是:

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 )这也将返回 #VALUE!我认为主要问题是与网站的连接不成功。

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

那么，为什么要加 myRequest.send将返回 #VALUE! ?

有什么指导吗？

太感谢了。我已经为此工作了两天。
如果我更改 URL 并设置 uu等于另一个地理编码网站，没有问题。
那么网站有什么问题吗？(Microsoft Bing)
但我必须使用必应，如何处理？

谢谢，

Answer 1

Ajax 不是这里的问题。您可以加载并使用长路径访问:

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