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.
Thanks a lot for these awesome tutorials.
How do you ensure that xml is UTF-8 encoded?
Have You tried adding
< ?xml version="1.0" encoding="UTF-8"? >
at the beginning of the xml file?
I rather print the document using DOM printer. Roughly:
Dim xmlDoc As Object, root As Object
Dim nod0, nod1, nod2, nod3… As Object
Set xmlDoc = CreateObject(“MSXML2.DOMDocument”)
‘encoding
Set xmlNode = xmlDoc.createProcessingInstruction(“xml”, “cersion=’1.0’encoding = “‘UTF-8′”)
Set xmlNode = xmlDoc .InsertBefore(xmlNode, xmlDoc.ChildNodes.Item(0))
‘ children
Set nod0 = xmlDoc.crreateElement(“Root”)
xmlDoc.appendChild nod0
Set nod1 = xmlDoc.createElement(“childOne”)
nod0.appendChild nod1
‘etc etc
xmlDoc.Save PATH_TO_SAVE_XML_FILE
You can add atrtibutes by SetAttribute and node value by node.text
Appreciate your input Karol! 🙂