title: How read certain XML data into Excel via VBA
body: <p>I got a XML response text from Google Map Distance Matrix API that I have to read it into Excel or message out certain information from the XML response text. I will just need the Value in <code>&lt;Status&gt;</code>, <code>&lt;Text&gt;</code> in duration and <code>&lt;Text&gt;</code> in distance.</p>
  
  <p>This is my VBA code:</p>
  
  <pre><code>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
  
  Dim num1 As String
  
  Set wb = ActiveWorkbook
  Set ws = wb.Sheets("Sheet1")
  
  Set htm = CreateObject("htmlFile")
  num1 = Cells(2, 2).Value
  
  With CreateObject("msxml2.xmlhttp")
      .Open "GET", "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=sy8 2jp&amp;destinations=" &amp; num1 &amp; "&amp;mode=driving&amp;language=en-GB&amp;v=3&amp;sensor=false&amp;units=imperial", False
      .send
      htm.body.innerHTML = .responseText
      Debug.Print .responseText (don't know how to debug print certain value)
  
      MSGbox (.responseText)(or strip out certain value of response text)
  End With
  
  End Sub
  </code></pre>
  
  <p>This is my debug print response text </p>
  
  <pre><code> &lt;status&gt;OK&lt;/status&gt;
   &lt;origin_address UK&lt;/origin_address&gt;
   &lt;destination_address&gt;UK&lt;/destination_address&gt;
   &lt;row&gt;
    &lt;element&gt;
      &lt;status&gt;OK&lt;/status&gt;
        &lt;duration&gt;
        &lt;value&gt;622&lt;/value&gt;
        &lt;text&gt;10 mins&lt;/text&gt;
        &lt;/duration&gt;
     &lt;distance&gt;
       &lt;value&gt;8552&lt;/value&gt;
       &lt;text&gt;5.3 mi&lt;/text&gt;
     &lt;/distance&gt;
   &lt;/element&gt;
  &lt;/row&gt;
  &lt;/DistanceMatrixResponse&gt;
  </code></pre>