XLSX to XML Converting through Macro VBA

[tanmay2501]

HI All,
Can anyone help me out with the Macro that converts that XLSX to XML through VBA on a button click
There would be Nodes in the Data you can seek the help from example below
(Also look in the blank value generation in the xml with only closing tag )

For eg:-
Sheet 1 contains the following data
Column 1 Column 2 Column 3 Column 4
Row 1 Data\School\Name Data\School\Area Data\Affiliation\Type Data\Affiliation\Code
Row 2 ABC Pune CBSE 001
Row 3 XYZ Blank ICSE 002


XML File after conversion
<Data>
<School>
<Name>ABC</Name>
<Area>Pune</Area>
</School>
<Affiliation>
<Type>CBSE</Type>
<Code>001</Code>
</Affiliation>
</Data>
<Data>
<School>
<Name>XYZ</Name>
</Area> (Notice Here Only the end tag is here as there is no data in the sheet for area in the 2nd row)
</School>
<Affiliation>
<Type>ICSE</Type>
<Code>002</Code>
</Affiliation>
</Data>


Thanks for the Help

[Aussiebear]

Maybe this might work as a general concept?

Sub ConvertToXML()
    ' Declare variables
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim xmlFileName As String
    Dim lastRow As Long
    Dim i As Long
    ' Set the workbook and worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1") 
    ' Replace "Sheet1" with your sheet name
    ' Set the XML file name
    xmlFileName = "C:\Path\To\Your\File\Output.xml" 
    ' Replace with your desired path
    ' Open the XML file for output
    Open xmlFileName For Output As #1
    ' Write XML header  Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    ' Get the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row and write data to XML
    For i = 1 To lastRow    Print #1, "<Row>"
        ' Loop through each column in the row
        For j = 1 To ws.Cells(i, ws.Columns.Count).End(xlToLeft).Column
            Print #1, "  <Column" & j & ">" & ws.Cells(i, j).Value & "</Column" & j & ">"
        Next j
        Print #1, "</Row>"
    Next i
    ' Close the XML file
    Close #1
    ' Message box to indicate successful conversion
    MsgBox "Excel sheet successfully converted to XML.", vbInformation
End Sub