excel - 使用 vba 从 Google Maps API 获取数据

Question

我正在尝试使用 google maps api 获取 excel 以提取驾驶时间和距离。我根本无法让它提取任何数据。

Sub main()
    Dim a, b, i, Str As String
    Dim lineS As Variant
    On Error Resume Next
    'Application.ScreenUpdating = False
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        a = CStr(ThisWorkbook.Worksheets(1).Range("B3"))
        Dim iRow As Long: iRow = ThisWorkbook.Worksheets(1).Range("G6500").End(xlUp).Row
        For j = 5 To iRow
            b = ThisWorkbook.Worksheets(1).Range("R" & j)
            .Open "GET", "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & a & "&destinations=" & b & "&mode=driving&key=[hidden from this post]", False
            .send
            lineS = Split(.responsetext, vbLf)
            linS = .response
                For k = 25 To UBound(lineS)
                    If Trim(lineS(k)) = """distance"" : {" Then
                        Exit For
                    End If
                Next k
            ThisWorkbook.Worksheets(1).Range("s" & j) = lineS(k + 1)
            ThisWorkbook.Worksheets(1).Range("t" & j) = lineS(k + 5)
            Application.Wait (Now + TimeValue("0:00:01"))
        Next j
    End With
    'Application.ScreenUpdating = True

End Sub

该 api 在浏览器中工作并且看起来像这样：

{
   "destination_addresses" : [ "Aberdeen AB11, UK" ],
   "origin_addresses" : [ "Harwich CO12, UK" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "899 km",
                  "value" : 898569
               },
               "duration" : {
                  "text" : "9 hours 32 mins",
                  "value" : 34292
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

任何帮助深表感谢。

标签： excelvba