title: Excel vba Parse Complex XML
body: <p>I need to get data from an xml file and load it to an Excel worksheet. I'm able to load the xml file and get element data in messagebox. This will written in VBA for Excel 2013</p>
  
  <p>the xml file structure </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt;
  &lt;Response &gt;
      &lt;Status&gt;10&lt;/Status&gt;
      &lt;callMessage&gt;Success.&lt;/callMessage&gt;
      &lt;PullR version="1"&gt;
          &lt;responseStatus&gt;1000&lt;/responseStatus&gt;
          &lt;responseMessage&gt;Success.&lt;/responseMessage&gt;
          &lt;Options&gt;
              &lt;Option&gt;
                  &lt;header&gt;
                      &lt;need1&gt;text1&lt;/need1&gt; ---- "COLUMN1"
                      &lt;need2&gt;
                          &lt;![CDATA[text2]]&gt;
                      &lt;/need2&gt;  ---- "COLUMN2"
                      &lt;need 3&gt;this text3 together with need 2&lt;/need3&gt;"COLUMN2"
                      &lt;need4&gt;
                          &lt;![CDATA[Text4]]&gt;
                      &lt;/need4&gt; ---"COLUMN4"
                  &lt;/header&gt;
                  &lt;Details&gt; - ""this one could be 1 to 10 child element (detail) this sample has 2 ""
                      &lt;detail&gt;
                          &lt;need5&gt;text5&lt;/need5&gt;  " COLUMN5"
                          &lt;need6&gt;date6&lt;/need6&gt;
                          &lt;need7&gt;time7&lt;/need7&gt;
                          &lt;need8&gt;
                              &lt;![CDATA[text8]]&gt;
                          &lt;/need8&gt;
                          &lt;/detail&gt;
                      &lt;detail&gt; ---- ""this should be on second row in excel sheet ""
                          &lt;need5&gt;text5&lt;/need5&gt; --- "COLUMN5 ROW +1"
                          &lt;need6&gt;date6&lt;/need6&gt; ---- "COLUMN6 ROW +1"
                          &lt;need7&gt;time7&lt;/need7&gt;
                          &lt;need8&gt;
                              &lt;![CDATA[text8]]&gt;
                          &lt;/need8&gt;
                      &lt;/detail&gt;
                  &lt;/Details&gt;
              &lt;/Option&gt;
           &lt;/Options&gt;
      &lt;/PullR&gt;
  &lt;/Response&gt;
  </code></pre>
  
  <p>I reduce the xml file child elements there is many more , I'm stuck on the detail parent element part as when it writes the 2nd row it writes need1 for the next Option ancestor element. 
  The date in the cell will be the text (atomic value) field which I inputed exactly as there are in the xml file.</p>
  
  <hr>
  
  <pre><code>Dim Options As IXMLDOMNodeList
  Dim Option As IXMLDOMNode
  
  Set Options = rosDoc.SelectNodes("/Response/PullR/Options/Option")
  
     For Each Option In Options
  
     MsgBox Trip.SelectNodes("header/need1").Item(0).Text
     MsgBox Trip.SelectNodes("Details/Detail/need5").Item(0).Text
     MsgBox Trip.SelectNodes("Details/Detail/need5").Item(1).Text
     Next
  </code></pre>
  
  <p>This code works perfect but need5 is variable most cases there is 2 items but sometimes there might be more how will I go by making a count off need5 within option</p>