How to extract Google Distance JSON response text into Excel Cells using VBA

Question

I got a button on excel to run a Google Distance Service to Calculate how many miles and the duration of the travelling time is.

When I run the service I get the following response text, I wondering how can I extract just the Status, Distance, Duration from JSON response text and copy into my excel worksheet cells.

the following is the response text I got from the Google Distance service.

{
"destination_addresses" : [ "San Francisco, Californie, États-Unis", "Victoria, BC, Canada" ],
"origin_addresses" : [ "Vancouver, BC, Canada", "Seattle, Washington, États-Unis" ],
"rows" : [
  {
     "elements" : [
        {
           "distance" : {
              "text" : "1 709 km",
              "value" : 1709333
           },
           "duration" : {
              "text" : "3 jours 19 heures",
              "value" : 327629
           },
           "status" : "OK"
        },
        {
           "distance" : {
              "text" : "135 km",
              "value" : 134569
           },
           "duration" : {
              "text" : "6 heures 22 minutes",
              "value" : 22946
           },
           "status" : "OK"
        }
     ]

Sub Button1_Click()
    Dim x As Long, y As Long
    Dim htm As Object
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim TxtRng  As Range

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet1")


    Set htm = CreateObject("htmlFile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "https://maps.googleapis.com/maps/api/distancematrix/json?origins=sy3 3jp&destinations=hr3 3ly&mode=driving&language=en-GB&v=3&sensor=fals", False
        .send
        htm.body.innerhtml = .responseText      
        Debug.Print .responseText
    End With




End Sub

If you search here for "VBA JSON" you'll see some responses to previous questions on this topic - try one of those and post back if you run into problems. — Tim Williams, Jul 25 '14 at 15:01
Try to find any VBA JSON related to my topic, no muck luck tho. I just want the to get the destination addresses value into Cell C12 with origin addresses Cell C13 Etc — Steve, Jul 28 '14 at 14:30

m82amjad · Answer 1 · 2014-07-25T14:05:15.293

0

Please try the following it gives me the value. It should give you too .. This are some example. Please let me know if you need further help getting any specific value.

  $url ="https://maps.googleapis.com/maps/api/distancematrix/json?origins=GU215ed&destinations=n90dy&mode=driving&language=en-GB&v=3&sensor=false";
 $data = @file_get_contents($url);


 $data = json_decode($data );
 echo $data->origin_addresses[0];  // This will give you origin_addresses
 echo $data->status;   // Will give you status == OK
 echo $data->rows[0]->elements[0]->distance->text;  // Will give you distance  in text
 echo $data->rows[0]->elements[0]->duration->text;  // Will give you distance  in text

edited Jul 25 '14 at 14:05

answered Jul 25 '14 at 13:59

m82amjad

248
2
9

Thanks for your comment, how can direct this data into excel cell, like Distance Text of 1709 km into Cell D1 Etc. much appreciated. – Steve Jul 25 '14 at 14:06
You need you use some excel library like 'PHPExcel' http://stackoverflow.com/questions/3968973/how-can-i-write-data-into-an-excel-using-php Let me know if you need help on that too – m82amjad Jul 25 '14 at 14:10
http://stackoverflow.com/questions/24126659/vba-excel-how-to-fill-in-two-cells-in-a-row-with-text?rq=1 Please have a look at this one ... – m82amjad Jul 25 '14 at 14:13
How does this help the OP ? They are clearly working in Excel with VBA: this is JSON-parsing code in some other language (which you don't even specify). – Tim Williams Jul 25 '14 at 15:14
This is PHP Code, But the idea is here, If you need further help I can try helping you even thought I do not work with VBA. – m82amjad Jul 25 '14 at 16:22

How to extract Google Distance JSON response text into Excel Cells using VBA

1 Answers1