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:
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 <> 200
DoEvents
Wend
If Not objXML.LoadXML(XMLRequest.responseText) Then
Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
End If
End Sub
I am using the Microsoft XML V6.0 reference.
The XML response is quite large but this is the format of it:
<DataSet>
<xs:schema id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="CANSIM">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0"/>
<xs:element name="Date" type="xs:dateTime" minOccurs="0"/>
<xs:element name="Year" type="xs:int" minOccurs="0"/>
<xs:element name="Month" type="xs:int" minOccurs="0"/>
<xs:element name="BondYield_LongTerm_Gov" type="xs:double" minOccurs="0"/>
<xs:element name="BondYield_LongTerm_Prov" type="xs:double" minOccurs="0"/>
<xs:element name="BondYield_LongTerm_Corp" type="xs:double" minOccurs="0"/>
<xs:element name="B14070" type="xs:double" minOccurs="0"/>
<xs:element name="B14072" type="xs:double" minOccurs="0"/>
<xs:element name="B14081" type="xs:double" minOccurs="0"/>
<xs:element name="B14045" type="xs:double" minOccurs="0"/>
<xs:element name="Rolling12MonthAvg" type="xs:double" minOccurs="0"/>
<xs:element name="B14055" type="xs:double" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram>
<NewDataSet>
<CANSIM diffgr:id="CANSIM1" msdata:rowOrder="0">
<ID>462</ID>
<Date>2006-06-01T00:00:00-04:00</Date>
<Year>2006</Year>
<Month>6</Month>
<BondYield_LongTerm_Gov>0.0469</BondYield_LongTerm_Gov>
<BondYield_LongTerm_Prov>0.0518</BondYield_LongTerm_Prov>
<BondYield_LongTerm_Corp>0.0581</BondYield_LongTerm_Corp>
<B14070>0.0458</B14070>
<B14072>0.0467</B14072>
<B14081>0.019</B14081>
<B14045>0.0318</B14045>
<Rolling12MonthAvg>0.026425</Rolling12MonthAvg>
<B14055>0.0328</B14055>
</CANSIM>
<CANSIM diffgr:id="CANSIM2" msdata:rowOrder="1">
<ID>463</ID><Date>2006-07-01T00:00:00-04:00</Date><Year>2006</Year><Month>7</Month>
<BondYield_LongTerm_Gov>0.0446</BondYield_LongTerm_Gov>
<BondYield_LongTerm_Prov>0.0496</BondYield_LongTerm_Prov>
<BondYield_LongTerm_Corp>0.056</BondYield_LongTerm_Corp>
<B14070>0.0431</B14070>
<B14072>0.0445</B14072>
<B14081>0.018</B14081>
<B14045>0.0318</B14045>
<Rolling12MonthAvg>0.027216666667</Rolling12MonthAvg>
<B14055>0.0328</B14055>
</CANSIM>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
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.
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.
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.
