使用VBA方法通过Bing API获取距离不起作用 [英] Get distance via bing api using VBA method is not working

问题描述

当我使用#NAME吗?有时#value，可能是什么原因请指导

I am trying to create a method via vba when I use this method says #NAME? and sometime #value, what could the be cause please guide

谢谢

Public Function DISTANCE(start As String, dest As String, key As String)
    
        Dim firstVal As String, secondVal As String, lastVal As String
    
        firstVal = "http://dev.virtualearth.net/REST/V1/Routes/Driving?wp.0="
        secondVal = "&wp.1=destinations="
        lastVal = "&optimize=time&routePathOutput=Points&distanceUnit=km&output=xml&key=" & key
    
        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 ("")
        DISTANCE = Round(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.609, 0)
    
    End Function

推荐答案

三件事.

1. 您需要将小写字母和状态字母(两个字母缩写)一起传递给

1. You need to pass to and from locations as lower case along with , 2 letter abbr for state

上面的内容需要使用Urlencoded

The above need to be urlencoded

响应具有您需要添加的默认名称空间.对于后者，我求助于使用MSXML2.DOMDocument以便添加名称空间.

The response has a default namespace you need to add in. For the latter, I resort to using MSXML2.DOMDocument so as to be able to add the namespace.

---

Public Sub test()

    Debug.Print GetDistance("new york,ny", "miami,fl", "key")

End Sub

Public Function GetDistance(ByVal start As String, ByVal dest As String, ByVal key As String) As Long
    
    Dim firstVal As String, secondVal As String, lastVal As String, objHTTP As Object, url As String
    
    firstVal = "http://dev.virtualearth.net/REST/V1/Routes/Driving?wp.0="
    secondVal = "&wp.1=destinations="
    lastVal = "&optmz=time&routePathOutput=Points&distanceUnit=km&output=xml&key=" & key
    
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    url = firstVal & Application.EncodeURL(LCase$(start)) & secondVal & Application.EncodeURL(LCase$(dest)) & lastVal
    
    objHTTP.Open "GET", url, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send

    Dim xmlDoc As MSXML2.DOMDocument60 'reference to Microsoft XML via VBE>Tools>References
    
    Set xmlDoc = New MSXML2.DOMDocument60
    xmlDoc.LoadXML objHTTP.responseText
    xmlDoc.SetProperty "SelectionNamespaces", "xmlns:r='http://schemas.microsoft.com/search/local/ws/rest/v1'"

    GetDistance = Round(Round(xmlDoc.SelectSingleNode("//r:TravelDistance").Text, 3) * 1.609, 0)
    
End Function

这篇关于使用VBA方法通过Bing API获取距离不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！