How to create XML file from scratch?

This is the third and the last part from the XML trilogy. Be sure You didn’t miss how to convert XML into Excel and how to overwrite XML file without copying or converting. In this article I’m going to show You how to create XML file from scratch.

The case

Once again, take a look at the prepared XML file structure.

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

This is our goal. We want to achieve this starting from the Excel table showed below.

Product	FEATURE1	FEATURE2	FEATURE3
A	A1	A2	A3
B	B1	B2	B3
C	C1	C2	C3

First theory

So let’s call the whole worksheet as catalog filled with products. Products are stored only in the first column, features are in the rest of the columns.

So, the child nodes of the catalog (worksheet) are products (1st column). Features are child nodes of the products. Summing up – 3 levels. The first one is known, there is only 1 catalog, which opens in the beginning and closes in the end. Second is products and the third one is features.

We can see how many products and features are in the table, but let’s write the code universal, in case of change.

Practice!

To start coding in the first place You need your future XML file path.

xml_file = "...\test2.xml"

Then create the file and mark it as output with number.

Open xml_file For Output As #1

Now every time You want to write a new line in the xml_file just put Print method and type the number You chose above with hashtag.

Let’s start manually with typing CATALOG.

Print #1, "<CATALOG>"

Now the hardest part. You need 2 loops – for columns and rows. While looping in columns remember, that first column is the name of the product – start with Product tag, in the second the features begin and after the last feature (last column) You need to end with Product tag. Just like in the XML structure I showed You up there.

For i = 2 To lastRow
    For j = 1 To lastCol
        head = UCase(.Cells(1, j))
        val = .Cells(i, j)
        If j = 1 Then
            prod = head
            Print #1, "<" & prod & " name=""" & val & """>"
        Else
            Print #1, "<" & head & ">" & val & "</" & head & ">"
            If j = lastCol Then
                Print #1, "</" & prod & ">"
            End If
        End If
    Next
Next

In the last part, after all loops, You need to close the CATALOG and close the output xml_file.

Print #1, "</CATALOG>"
Close #1

Code

Full code below.

Option Explicit

Sub xml_creation()

Dim xml_file As String, head As String, val As String
Dim prod As String
Dim lastRow As Long, lastCol As Long, i As Long, j As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(1)
xml_file = "...\test2.xml"

Open xml_file For Output As #1
Print #1, "<CATALOG>"

With ws
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

    For i = 2 To lastRow
        For j = 1 To lastCol
            head = UCase(.Cells(1, j))
            val = .Cells(i, j)
            If j = 1 Then
                prod = head
                Print #1, "<" & prod & " name=""" & val & """>"
            Else
                Print #1, "<" & head & ">" & val _
                    & "</" & head & ">"
                If j = lastCol Then
                    Print #1, "</" & prod & ">"
                End If
            End If
        Next
    Next

End With

Print #1, "</CATALOG>"
Close #1

End Sub

Some end words

And this is it! End of the last part from this XML trilogy! Now You should know how to create XML file from scratch along with converting XML into Excel and overwriting XML. I hope You enjoyed that and those articles will be helpful at least a little bit.