How to export Excel data in XML format with VBA

In today's digital landscape, the need to exchange data between different systems is paramount.

XML (eXtensible Markup Language) files have been around for a long time and have proven to be convenient and versatile. They have become a key solution for facilitating the exchange of structured data across diverse platforms and applications.

When Excel data has to be fed to other systems, the export to XML format is a must 

This post will explain how to do it quickly and easily with the help of VBA

1 Structure of a XML file 

XML file have a defined structure The key components of this structure are the Root and the Elements

Within the Root elements you can have sub Roots

The example used in this post is a Library in which different books are described in accordance with their Category, Title, Author and Price

The library is the first Root Element and the Book is the second Root element

Within the Book Root element we will find the Elements of every single book: Category, Title, Author, Price

2 Code Output 

Once the code has been executed the sheet "XML" of the file will be reporting all the data of the sheets "Books" in the XML format. All the populated cells od the sheet "XML" have to be copied in txt file. 

Before saving the txt file the file extension needs to be changed to XML

When the file is reopened it will feature all the XML characteristics

The below pictures show the described steps

3 Full Code

Below the full code is reported 

Sub CreateXML()

' it is assumed that the active sheet is the one called Books

Dim wss As Sheets

Dim ws As Worksheet

Set wss = ThisWorkbook.Worksheets

Set ws = wss("XML")

'all data present in the sheet XML is cancelled

Sheets("XML").Cells.Delete

' identify the last used row. It is assumed that there are no more than 100000 used rows

Sheets("Books").Select

Range("A100001").Select

lstrw = Selection.End(xlUp).Row

' the data uses 4 columns

lstcol = 4

'define the variable that will determine the row number in which the text of cells in sheet Books

'will be written in the XML sheet

r = 2

'The library  is a root element

ws.Cells(1, 1).Value = "<Library>"

' Loop through the rows and create the corresponding entry in the TXT file

For i = 2 To lstrw

'2 spaces are inserted before the Root book in order to create the indentation

    ws.Cells(r, 1) = "  <Book>"

    r = r + 1

 '5 spaces are inserted before the following lines in order to create an indentation

    Mystring = Space(5)    

        For k = 1 To lstcol

            ws.Cells(r, 1).Value = Mystring & "<" & Cells(1, k).Value & ">"

            ws.Cells(r, 1).Value = ws.Cells(r, 1).Value & Cells(i, k).Value

            ws.Cells(r, 1).Value = ws.Cells(r, 1).Value & "</" & Cells(1, k).Value & ">"

            r = r + 1

        Next k

       ws.Cells(r, 1).Value = "  </Book>"

    r = r + 1

Next i

ws.Cells(r, 1).Value = "</Library>"

Sheets("XML").Select

Range("a1").Select

Range(Cells(1, 1), Cells(r, 1)).Select

Selection.Copy

End Sub