title: Google Api in Excel to calculate Driving Distance between places
body: <p>I have about 20 K pairs to calculate driving distance between them. I am using following VB script -</p>
  
  <pre><code>'Calculate Google Maps distance between two addresses
  Public Function GetDistance(start As String, dest As String)
      Dim firstVal As String, secondVal As String, lastVal As String
      firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?units=metric&amp;origins="
      secondVal = "&amp;destinations="
      lastVal = "&amp;mode=car&amp;language=en&amp;sensor=false&amp;key=A***Bh*Eh-g***LvJ7bRirvjlr****OkUvs"
      Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
      Url = firstVal &amp; Replace(start, " ", "+") &amp; secondVal &amp; Replace(dest, " ", "+") &amp; lastVal
      objHTTP.Open "GET", Url, False
      objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
      objHTTP.send ("")
      If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
      Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
      Set matches = regex.Execute(objHTTP.responseText)
      tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
      GetDistance = CDbl(tmpVal)
      Exit Function
  ErrorHandl:
      GetDistance = -1
  End Function
  </code></pre>
  
  <p>When I write <code>=GetDistance("Atlanta", "Miami")</code> it throws <code>-1</code> (Error). However, when I paste the URL in browser, it works. </p>