Easy way to convert XML into Excel

转载：Easy way to convert XML into Excel - Simple Excel VBA

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<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.

1
2

Set xmlfile = CreateObject("Microsoft.XMLDOM")
xmlfile.Load (xmlfilename)

If You know the file structure use specified node.

1

Set ProductNodes = xmlfile.SelectNodes("/CATALOG/PRODUCT")

In other case, when You don’t really know what You can expect inside, use only slash.

1

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.

1

For i = 0 To ProductNodes.Length - 1

Always.

1

ProductNodes.Length - 1

To get the name, type, id or whatever attribute You want to get use .getattribute() method.

1

.getattribute("name")

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.

# Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

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

# The result

# 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.