title: Get values in XML response in VBA
body: <p>I am using a GET request to get an XML response from a web service.  I'm attempting to parse the response and display a portion of it in a VBA form. The VBA components I think I have a good handle on but the XML syntax is giving me quite a bit of problems.  This is the sub I have in VBA so far:</p>
  
  <pre><code>Sub GetCANSIM()
  Dim XMLRequest As New XMLHTTP
  Dim objXML As MSXML2.DOMDocument
  Dim point As IXMLDOMNode
  Dim value As Variant
  Dim lngYear As Long
  
  Set objXML = New MSXML2.DOMDocument
  
  XMLRequest.Open "GET", "someURL", False
  XMLRequest.send
  
  While XMLRequest.Status &lt;&gt; 200
      DoEvents
  Wend
  
  If Not objXML.LoadXML(XMLRequest.responseText) Then
      Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
  End If
  
  End Sub
  </code></pre>
  
  <p>I am using the Microsoft XML V6.0 reference.</p>
  
  <p>The XML response is quite large but this is the format of it:</p>
  
  <pre><code>&lt;DataSet&gt;
  &lt;xs:schema id="NewDataSet"&gt;
  &lt;xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"&gt;
  &lt;xs:complexType&gt;
  &lt;xs:choice minOccurs="0" maxOccurs="unbounded"&gt;
  &lt;xs:element name="CANSIM"&gt;
  &lt;xs:complexType&gt;
  &lt;xs:sequence&gt;
  &lt;xs:element name="ID" type="xs:int" minOccurs="0"/&gt;
  &lt;xs:element name="Date" type="xs:dateTime" minOccurs="0"/&gt;
  &lt;xs:element name="Year" type="xs:int" minOccurs="0"/&gt;
  &lt;xs:element name="Month" type="xs:int" minOccurs="0"/&gt;
  &lt;xs:element name="BondYield_LongTerm_Gov" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="BondYield_LongTerm_Prov" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="BondYield_LongTerm_Corp" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="B14070" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="B14072" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="B14081" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="B14045" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="Rolling12MonthAvg" type="xs:double" minOccurs="0"/&gt;
  &lt;xs:element name="B14055" type="xs:double" minOccurs="0"/&gt;
  &lt;/xs:sequence&gt;
  &lt;/xs:complexType&gt;
  &lt;/xs:element&gt;
  &lt;/xs:choice&gt;
  &lt;/xs:complexType&gt;
  &lt;/xs:element&gt;
  &lt;/xs:schema&gt;
  &lt;diffgr:diffgram&gt;
  &lt;NewDataSet&gt;
  &lt;CANSIM diffgr:id="CANSIM1" msdata:rowOrder="0"&gt;
  &lt;ID&gt;462&lt;/ID&gt;
  &lt;Date&gt;2006-06-01T00:00:00-04:00&lt;/Date&gt;
  &lt;Year&gt;2006&lt;/Year&gt;
  &lt;Month&gt;6&lt;/Month&gt;
  &lt;BondYield_LongTerm_Gov&gt;0.0469&lt;/BondYield_LongTerm_Gov&gt;
  &lt;BondYield_LongTerm_Prov&gt;0.0518&lt;/BondYield_LongTerm_Prov&gt;
  &lt;BondYield_LongTerm_Corp&gt;0.0581&lt;/BondYield_LongTerm_Corp&gt;
  &lt;B14070&gt;0.0458&lt;/B14070&gt;
  &lt;B14072&gt;0.0467&lt;/B14072&gt;
  &lt;B14081&gt;0.019&lt;/B14081&gt;
  &lt;B14045&gt;0.0318&lt;/B14045&gt;
  &lt;Rolling12MonthAvg&gt;0.026425&lt;/Rolling12MonthAvg&gt;
  &lt;B14055&gt;0.0328&lt;/B14055&gt;
  &lt;/CANSIM&gt;
  &lt;CANSIM diffgr:id="CANSIM2" msdata:rowOrder="1"&gt;
  &lt;ID&gt;463&lt;/ID&gt;&lt;Date&gt;2006-07-01T00:00:00-04:00&lt;/Date&gt;&lt;Year&gt;2006&lt;/Year&gt;&lt;Month&gt;7&lt;/Month&gt;
  &lt;BondYield_LongTerm_Gov&gt;0.0446&lt;/BondYield_LongTerm_Gov&gt;  
  &lt;BondYield_LongTerm_Prov&gt;0.0496&lt;/BondYield_LongTerm_Prov&gt;
  &lt;BondYield_LongTerm_Corp&gt;0.056&lt;/BondYield_LongTerm_Corp&gt;
  &lt;B14070&gt;0.0431&lt;/B14070&gt;
  &lt;B14072&gt;0.0445&lt;/B14072&gt;
  &lt;B14081&gt;0.018&lt;/B14081&gt;
  &lt;B14045&gt;0.0318&lt;/B14045&gt;
  &lt;Rolling12MonthAvg&gt;0.027216666667&lt;/Rolling12MonthAvg&gt;
  &lt;B14055&gt;0.0328&lt;/B14055&gt;
  &lt;/CANSIM&gt;
  &lt;/NewDataSet&gt;
  &lt;/diffgr:diffgram&gt;
  &lt;/DataSet&gt;
  </code></pre>
  
  <p>I'd like to grab the last 60 months of the "B14045" rate and the "Rolling12MonthAvg" for the 12th month of the last 5 years.</p>
  
  <p>The values are all in order so I am thinking I should be able to "find" or "goto" the starting month (60 months ago) by using the "Month" and "Year" attributes and then reading the B14045 rate in an incrementing loop.</p>
  
  <p>But I don't know the syntax for finding the node(?) I want and then increment forward.  I've tried reading some help files and other topics but I can't seem to make heads or tails of it.  Any help would be greatly appreciated.</p>