返回错误json的Google API distancematrix导致VBA [英] Google API distancematrix returning wrong json result in VBA

问题描述

希望有人能指出我正确的方向。我在VBA中有一个函数，它从Excel调用，调用从一个邮政编码到另一个邮政编码的距离。这是正常的，直到现在。

距离只需要为荷兰计算，但不可能将查询字符串限制在一个国家/地区。现在我有一个邮政编码2151 KD，我用我的查询字符串作为一个从或从邮政编码。像：

http://maps.googleapis.com/maps/api/distancematrix/json?origins=1251KD&destinations=1211LW&mode=car&sensor=false

（我删除数字和字母之间的空格）。

当我进入浏览器。显示正确的位置和距离。 （Destionation or origin）。

此查询字符串使用此VBA函数在代码中生成：

 函数GetDistance（fromZip As String，toZip As String）As Integer 
 
 Dim Uri As String：Uri =http://maps.googleapis.com/maps/api/distancematrix / json？origins = {0}& destinations = {1}& mode = car& sensor = false
 Dim xmlHttp As Object：Set xmlHttp = CreateObject（MSXML2.ServerXMLHTTP.6.0）
 Dim Json As Object 
 Dim distance As Double：distance = 0 
 
 Uri = Replace（Uri，{0}，fromZip）
 Uri = Replace（Uri， {1}，toZip）
 
使用xmlHttp 
。打开GET，Uri，False 
 .setRequestHeaderContent-Type，text / xml
 .send 
结束
 
'Debug.Print（Uri）
设置Json = JsonConverter.ParseJson（xmlHttp.ResponseText）
 
'级别状态OK 
如果Json（status）=OK然后
'Elementstatus 
如果Json（rows）（1）（elements）（1） ）=OK然后
d istance = CLng（Json（rows）（1）（elements）（1）（distance）（value））/ 1000 
 Else 
 distance = b End If 
 Else 
 distance = -1 
 End If 
 
如果fromZip =1251KD或toZip =1251KD然后
 Debug.Print （xmlHttp.ResponseText）
 Debug.Print（Uri）
如果
 
如果IsObject（xmlHttp）然后
设置xmlHttp = Nothing 
 End If 
 
如果IsObject（Json）Then 
 Set Json = Nothing 
 End If 
 
 GetDistance = Round（distance，0）
  

结束功能

但是结果不同。公元1251年现在是美国的一个地方。没有距离计算。

从VBA代码调用时的结果：

 destination_addresses：[1211 LW Hilversum，Netherlands]，
origin_addresses：[NE 1251，Osceola，MO 64776，USA]，
 ：[
 
 {
元素：[
 {
状态：ZERO_RESULTS
} 
] 
 
]，
状态：OK
} 
  

从浏览器直接调用结果：

  {
 destination_addresses：[
1211 LW Hilversum ，Nederland
，
 origin_addresses：[
1251 KD Laren，Nederland
]，
 rows：[
 {
 elements ：[
 {
 distance：{
 text：6,9 km，
 value：6878 
}，
 duration：{
文本：14分钟，
值：810 
}，
状态：确定
} 
] 
} 
 ]，
状态：确定
} 
  

到目前为止，我还没有弄清楚如何解决这个问题。当我将其粘贴到浏览器的地址栏中时，querystring正在工作，但是当从VBA调用时，结果无效。到目前为止这个特定邮政编码为1251 KD。

有没有人可以找到？

解决方案

对不起，我不能复制你的错误 - 代码工作正常。

但是，如果你只需要荷兰的结果，那你为什么不添加国家查询字符串？ p>

对于你的代码，这意味着

  Uri = Replace（Uri， {0}，fromZip&，Netherlands）
 Uri = Replace（Uri，{1}，toZip&，Netherlands）
  / pre> 
hope someone can point me in the right direction. I have a function in VBA which is called from Excel, to call the distance from one postal code to another. This is working fine, until now.

The distance only have to be calculated for The Netherlands, but it is not possible to limit the query-string to a country. Now I have a postal code 2151 KD, I use it in my querystring as a from or to postal code. like: 

http://maps.googleapis.com/maps/api/distancematrix/json?origins=1251KD&destinations=1211LW&mode=car&sensor=false

(I remove the space between the numbers and letters).

This works fine when I past in in the browser. Is shows the right locations and distance. (Destionation or origin).

This querystring is generated in code using this VBA function:
Function GetDistance(fromZip As String, toZip As String) As Integer

Dim Uri As String: Uri = "http://maps.googleapis.com/maps/api/distancematrix/json?origins={0}&destinations={1}&mode=car&sensor=false"
Dim xmlHttp As Object: Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Dim Json As Object
Dim distance As Double: distance = 0

Uri = Replace(Uri, "{0}", fromZip)
Uri = Replace(Uri, "{1}", toZip)

With xmlHttp
    .Open "GET", Uri, False
    .setRequestHeader "Content-Type", "text/xml"
    .send
End With

'Debug.Print (Uri)
Set Json = JsonConverter.ParseJson(xmlHttp.ResponseText)

'Top-level status OK
If Json("status") = "OK" Then
    'Elementstatus
    If Json("rows")(1)("elements")(1)("status") = "OK" Then
        distance = CLng(Json("rows")(1)("elements")(1)("distance")("value")) / 1000
    Else
        distance = -2
    End If
Else
    distance = -1
End If

If fromZip = "1251KD" Or toZip = "1251KD" Then
    Debug.Print (xmlHttp.ResponseText)
    Debug.Print (Uri)
End If

If IsObject(xmlHttp) Then
    Set xmlHttp = Nothing
End If

If IsObject(Json) Then
    Set Json = Nothing
End If

GetDistance = Round(distance, 0)
End Function

However the result is different. 1251 AD is now a location in the USA. And there is no distance calculation.

Result when called from VBA code:
{
  "destination_addresses" : [ "1211 LW Hilversum, Netherlands" ],
   "origin_addresses" : [ "NE 1251, Osceola, MO 64776, USA" ],
   "rows" : [

      {
         "elements" : [
            {
               "status" : "ZERO_RESULTS"
            }
         ]
      }
   ],
   "status" : "OK"
}
Result when called from browser directly:
{
destination_addresses: [
"1211 LW Hilversum, Nederland"
],
origin_addresses: [
"1251 KD Laren, Nederland"
],
rows: [
{
elements: [
{
distance: {
text: "6,9 km",
value: 6878
},
duration: {
text: "14 min.",
value: 810
},
status: "OK"
}
]
}
],
status: "OK"
}
So far I haven't been able to figure out how to solve this. The querystring is working when I paste it in the address-bar of the browser, but when called from VBA the result is not valid. So far just for this specific postal code of 1251 KD. 

Does anybody have a clou?
解决方案
Sorry, I cannot reproduce your error - the code works just fine.

But if you only need results from the Netherlands, why don't you add the country to the query string?

For your code, that would mean
Uri = Replace(Uri, "{0}", fromZip & ",Netherlands")
Uri = Replace(Uri, "{1}", toZip & ",Netherlands")


                        
这篇关于返回错误json的Google API distancematrix导致VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！