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