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.
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.
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.
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
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.
5 thoughts on “How to create XML file from scratch?”
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”)
Set xmlNode = xmlDoc.createProcessingInstruction(“xml”, “cersion=’1.0’encoding = “‘UTF-8′”)
Set xmlNode = xmlDoc .InsertBefore(xmlNode, xmlDoc.ChildNodes.Item(0))
Set nod0 = xmlDoc.crreateElement(“Root”)
Set nod1 = xmlDoc.createElement(“childOne”)
You can add atrtibutes by SetAttribute and node value by node.text
Appreciate your input Karol! 🙂