I've got data from WorldBank like this (source: http://api.worldbank.org/V2/country?incomeLevel=LIC) To use this VBA code you need to set References microsoft winhttp services microsoft xml microsoft 2.0 object library
<wb:countries xmlns:wb="http://www.worldbank.org" page="1" pages="1" per_page="50" total="34">
<wb:country id="AFG">
<wb:iso2Code>AF</wb:iso2Code>
<wb:name>Afghanistan</wb:name>
<wb:region id="SAS" iso2code="8S">South Asia</wb:region>
<wb:adminregion id="SAS" iso2code="8S">South Asia</wb:adminregion>
<wb:incomeLevel id="LIC" iso2code="XM">Low income</wb:incomeLevel>
<wb:lendingType id="IDX" iso2code="XI">IDA</wb:lendingType>
<wb:capitalCity>Kabul</wb:capitalCity>
<wb:longitude>69.1761</wb:longitude>
<wb:latitude>34.5228</wb:latitude>
</wb:country>
My code:
Dim strURL As String
Dim ws As Worksheet
Set ws = Worksheets("API")
strURL = ws.[API_URL]
Dim hReq As New WinHttpRequest
hReq.Open "GET", strURL, False
hReq.Send
Dim strResp As String
strResp = hReq.ResponseText
Dim xmlDoc As New MSXML2.DOMDocument
If Not xmlDoc.LoadXML(Right(strResp, Len(strResp) - 1)) Then
MsgBox ("Błąd ładowania URL")
End If
Dim xnodelist As MSXML2.IXMLDOMNodeList
Set xnodelist = xmlDoc.getElementsByTagName("wb:countries")
Dim xNode As MSXML2.IXMLDOMNode
Set xNode = xnodelist.Item(0)
Dim obAtt1 As MSXML2.IXMLDOMAttribute
Dim obAtt2 As MSXML2.IXMLDOMAttribute
Dim xChild As MSXML2.IXMLDOMNode
Dim xChild2 As MSXML2.IXMLDOMNode
Dim intRow As Integer
intRow = 3
Dim dtVal As String
Dim dblRate As String
Dim strVal As String
it works until here:
For Each xChild In xNode.ChildNodes
Set obAtt1 = xChild.Attributes.getNamedItem("id")
strVal = Trim(obAtt1.Text)
ws.Cells(intRow, 2) = obAtt1.Text
intRow = intRow + 1
Next xChild
it works only for first child - gets country code but I need for example to get wb:name (full name) I would be very thankful for any hints
Here's something which does what you want:
Dim xmlDoc As New MSXML2.DOMDocument
Dim countries As MSXML2.IXMLDOMNodeList, country As MSXML2.IXMLDOMNode
'need these next two....
xmlDoc.setProperty "SelectionLanguage", "XPath"
xmlDoc.setProperty "SelectionNamespaces", "xmlns:wb='http://www.worldbank.org'"
'loading from a local file for testing
If Not xmlDoc.Load(ThisWorkbook.Path & "\country.xml") Then
MsgBox ("Blad ladowania URL")
Exit Sub
End If
Set countries = xmlDoc.SelectNodes("//wb:country")
Debug.Print countries.Length
For Each country In countries
Debug.Print "------------------------------------"
Debug.Print "id", country.Attributes.getNamedItem("id").Text
Debug.Print "Name", country.SelectSingleNode("wb:name").nodeTypedValue
Debug.Print "Region", country.SelectSingleNode("wb:region").nodeTypedValue
'etc
Next country