Selecting and working with XML in Excel and VBA is always a bit challenging. Whenever a programmer (or a developer or an analyst or a portfolio controller) hears about XML and VBA, something weird happens in their minds. The programmer thinks that he should go back to Java (or C, C#, Python, etc.), the analyst thinks how to pass the task to the programming department and the portfolio controller think about going and asking of a better input. Even a *.csv file would be probably better.
Anyhow, lets imagine that our input XML data looks like this (the whole data is here):
So, our task is three-fold:
- Print the XML file within the FootballInfo node.
- Print only the information of the clubs. E.g. NorthClub, EastClub etc.
- Print only the node with name Row2.
Long story short, this is how the code looks like:
Dim xmlObj As Object
Set xmlObj = CreateObject("MSXML2.DOMDocument")
xmlObj.async = False
xmlObj.validateOnParse = False
Dim nodesThatMatter As Object
Dim node As Object
Set nodesThatMatter = xmlObj.SelectNodes("//FootballInfo")
For Each node In nodesThatMatter
'Task 1 -> print the XML file within the FootballInfo node:
Dim child As Variant
For Each child In node.ChildNodes
'Task 2 -> print only the information of the clubs. E.g. NorthClub, EastClub etc.
Dim singleNode As Object
Set singleNode = xmlObj.SelectSingleNode("//FootballInfo/row[@name='Row2']")
'Task 3 -> print only the node with name "Row2"
It is a good example of a needed usage for late binding, as far as the “MSXML2.DOMDocument” library is probably not included by default in the VBA project. In order to make it run, simply uncomment the corresponding task. And fix the correct path to the .Load file.