When I was young I thought, that if You want to send table data You use Excel. Now I know that there are tons of ways of transferring data. One of them is XML. In today’s article I will show You an easy way to convert XML into more user friendly Excel.
There is not one way of doing this..
I know. There are many methods of reading the XML file, just like in case of HTTP request. My first meeting with this topic was with Microsoft.XMLDOM and I want to introduce You to it, to learn how to read the XML files.
Example XML structure
For this occasion, specially for You, I prepared simple XML code. Our goal is to create code converting this into Excel table.
<CATALOG> <PRODUCT name="A"> <FEATURE1>A1</FEATURE1> <FEATURE2>A2</FEATURE2> <FEATURE3>A3</FEATURE3> </PRODUCT> <PRODUCT name="B"> <FEATURE1>B1</FEATURE1> <FEATURE2>B2</FEATURE2> <FEATURE3>B3</FEATURE3> </PRODUCT> <PRODUCT name="C"> <FEATURE1>C1</FEATURE1> <FEATURE2>C2</FEATURE2> <FEATURE3>C3</FEATURE3> </PRODUCT> </CATALOG>
Intro into code
First of all You need to create XML object.
Set xmlfile = CreateObject("Microsoft.XMLDOM") xmlfile.Load (xmlfilename)
If You know the file structure use specified node.
Set ProductNodes = xmlfile.SelectNodes("/CATALOG/PRODUCT")
In other case, when You don’t really know what You can expect inside, use only slash.
Set ProductNodes = xmlfile.SelectNodes("/")
To loop through XML tags use For loop. Always start from zero element and iterate to the number of elements minus one.
For i = 0 To ProductNodes.Length - 1
ProductNodes.Length - 1
To get the name, type, id or whatever attribute You want to get use .getattribute() method.
To loop through lower level element use .ChildNodes.
To get child node name use .BaseName and to get its value use .Text.
Before the code
I would like to mention about procedure I used down below in addition to XML properties and methods I told You above.
To read and transfer this XML data into Excel worksheet table I used 2 loops – for products (i) and for their features (j) (child nodes of products). I got name of the product (.getattribute(“name”)), name of the feature (.ChildNodes(j).BaseName) and its value (.ChildNode(j).Text).
Then using worksheet function .Find I searched for the number of column containing the feature name. In case of no such header I used error handling procedure (On error resume next) to create new column or put the value into existing one.
The result table is below the code.
Sub main() Dim xmlfile As Object, ProductNodes As Object Dim xmlfilename As String, prod_name As String, feat_name As String Dim feat_val As String Dim i As Long, j As Long, lastRow As Long, lastCol As Long Dim feat_col As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(1) xmlfilename = "...\test.xml" With ws .Cells(1, 1) = "Product" Set xmlfile = CreateObject("Microsoft.XMLDOM") xmlfile.Load (xmlfilename) Set ProductNodes = xmlfile.SelectNodes("/CATALOG/PRODUCT") For i = 0 To ProductNodes.Length - 1 lastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1 prod_name = ProductNodes.Item(i).getattribute("name") .Cells(lastRow, 1) = prod_name For j = 0 To ProductNodes.Item(i).ChildNodes.Length - 1 feat_name = ProductNodes.Item(i).ChildNodes(j).BaseName feat_val = ProductNodes.Item(i).ChildNodes(j).Text On Error Resume Next feat_col = .Rows(1).Find(LCase(feat_name)).Column If Err.Number <> 0 Then lastCol = .Cells(1, Columns.Count) _ .End(xlToLeft).Column + 1 .Cells(1, lastCol) = feat_name .Cells(lastRow, lastCol) = feat_val Else .Cells(1, feat_col) = feat_name .Cells(lastRow, feat_col) = feat_val End If On Error GoTo 0 Next Next End With MsgBox "Done" End Sub
Some words for the end
Of course this is just an example of possible approach to that issue. You could do this in million different ways. I dare You 🙂
Anyway, I hope I explained You most important and needed methods and properties of XMLDOM. Also, that simple example will help You to convert XML into Excel table right now, or in the future when You’ll need it.