title: Parse certain values from Google Maps XML to Excel cells
body: <p>I have a link <code>"https://maps.googleapis.com/maps/api/distancematrix/xml?origins=..."</code> that I access to get XML file data. </p>
  
  <p>XML file:</p>
  
  <pre><code>&lt;DistanceMatrixResponse&gt;
  &lt;status&gt;OK&lt;/status&gt;
  &lt;origin_address&gt;London, UK&lt;/origin_address&gt;
  &lt;destination_address&gt;Manchester, UK&lt;/destination_address&gt;
  &lt;row&gt;
  &lt;element&gt;
  &lt;status&gt;OK&lt;/status&gt;
  &lt;duration&gt;
  &lt;value&gt;14735&lt;/value&gt;
  &lt;text&gt;4 hours 6 mins&lt;/text&gt;
  &lt;/duration&gt;
  &lt;distance&gt;
  &lt;value&gt;335534&lt;/value&gt;
  &lt;text&gt;336 km&lt;/text&gt;
  &lt;/distance&gt;
  &lt;/element&gt;
  &lt;/row&gt;
  &lt;/DistanceMatrixResponse&gt;
  </code></pre>
  
  <p>XML file structure is always the same. I need to get <code>&lt;text&gt;4 hours 6 mins&lt;/text&gt;</code> and <code>&lt;text&gt;336 km&lt;/text&gt;</code> in form of <code>4,6</code> to cell A1 and <code>336</code> to cell A2 lets say "Contact database". Also problem here is that <code>&lt;text&gt;4 hours 6 mins&lt;/text&gt;</code> sometimes can be <code>&lt;text&gt;1 hour 3 min&lt;/text&gt;</code>. I can do it with formula but is it even possible with VBA?</p>
  
  <p>I have managed to make it work so that the whole XML file data is in cell A1. However not able to separate what I need and paste to two different cells.</p>
  
  <pre><code>Sub GoogleAPI1()
  Dim xmlhttp As Object
  Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")
  Dim myurl As String
  
  myurl = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=" &amp; ThisWorkbook.Worksheets("Contact database").Range("R86").Value _
  &amp; "&amp;destinations=" &amp; ThisWorkbook.Worksheets("Contact database").Range("R87").Value &amp; "&amp;mode=" &amp; ThisWorkbook.Worksheets("Contact database").Range("R88").Value _
  &amp; "&amp;key=" &amp; ThisWorkbook.Worksheets("Contact database").Range("R82").Value
  
  xmlhttp.Open "GET", myurl, False
  xmlhttp.send
  ThisWorkbook.Worksheets("Contact database").Range("R92") = xmlhttp.responseText
  End Sub
  </code></pre>