How to generate xml file from excel using macro

Generating XML File from Excel using Macro

Below is an example of how you can generate an XML file from an Excel worksheet using a macro:

    Sub ExportToXML()
        Dim ws As Worksheet
        Dim xmlFile As String
        Dim xmlData As String
        Dim lastRow As Long
        Dim i As Long
        
        ' Set the worksheet to export
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        
        ' Set the XML file name and location
        xmlFile = "C:\path\to\output.xml"
        
        ' Open the XML file for writing
        Open xmlFile For Output As #1
        
        ' Write the XML header
        Print #1, "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>"
        Print #1, "<root>"
        
        ' Get the last row in the worksheet
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
        ' Loop through each row in the worksheet
        For i = 2 To lastRow
            ' Build the XML data string
            xmlData = "<record>"
            xmlData = xmlData & "<name>" & ws.Cells(i, 1) & "</name>"
            xmlData = xmlData & "<age>" & ws.Cells(i, 2) & "</age>"
            xmlData = xmlData & "<email>" & ws.Cells(i, 3) & "</email>"
            xmlData = xmlData & "</record>"
            
            ' Write the XML data to the file
            Print #1, xmlData
        Next i
        
        ' Write the closing root tag
        Print #1, "</root>"
        
        ' Close the XML file
        Close #1
        
        MsgBox "XML file has been generated successfully."
    End Sub
  

Make sure to replace the worksheet name (“Sheet1”) and the file path (“C:\path\to\output.xml”) in the code with your own values.

This macro will iterate through each row in the worksheet after the header row, build the XML data string using the values from each row, and write it to the XML file. The resulting XML file will have a root element containing multiple record elements, each representing a row in the worksheet.