Excel table to XML Vba converter is putting all my data rows into one tag as attributes?

Question

I asked this question yesterday and got no answers. I am trying to transform and excel table into XML. Below is what I currently have (number of children reduced but overall format is this repeated). The issue I currently have is when I increase the number of rows, it simply gives me this.

<ROOT>
 <RANDOM>
  <RANDOM1 a="a2" b="b2" <RANDOM1 a='a3' b="b3" >
    <COOKIE>
     <ORANGE c="c2" d="d2" <ORANGE c="c3" d="d3"/>
    </COOKIE>
  </RANDOM1>
 <RANDOM>
<ROOT> 

I would like this format.

<ROOT>
 <RANDOM>
  <RANDOM1 a="a2" b="b2">
    <COOKIE>
     <ORANGE c="c2" d="d2"/>
    </COOKIE>
  </RANDOM1>
  <RANDOM1 a='a3' b="b3" >
    <COOKIE>
     <ORANGE c="c3" d="d3"/>
    </COOKIE>
  </RANDOM1>
 <RANDOM>
<ROOT>

Basically, how do I get the data to appear consecutively instead of all together in one line? My code is below.

Sub ToXML()
MyFile = "data.xml"
fnum = FreeFile()
Dim n As Integer
n = 2 'Number of DATA rows
Dim m As Integer
m = 104 ' Number of columns

Print #fnum, "<ROOT>" 
Print #fnum, "  < RANDOM >" 

For i = 2 To n + 1 

    Print #fnum, "    <RANDOM1 ";
    For j = 1 To 39
        Print #fnum, Cells(1, j).Value & "=""" & Cells(i, j).Value & """ ";
            Next j
            Next i


Print #fnum, ">" 
Print #fnum, "      < COOKIE >" 

    For i2 = 2 To n + 1 
            Print #fnum, "        <ORANGE ";
            For j2 = 40 To 42
                Print #fnum, Cells(1, j2).Value & "=""" & Cells(i, j2).Value & """ ";
                    Next j2
                    Next i2

Print #fnum, "/>" 
Print #fnum, "      <COOKIE>"
Print #fnum, "    RANDOM1>"
Print #fnum, "  </RANDOM>" 
Print #fnum, "</ ROOT >" 'Close root node

Answer 1

You should work with arrays and build each attribute before writing the row:

Dim headers(), data(), attributes1(), attributes2(), attr$, r&, c&

' load the headers and data to an array '
headers = Cells(1, 1).Resize(1, 42).value
data = Cells(1, 2).Resize(2, 42).value

' set the size for the attributes '
ReDim attributes1(1 To 39)
ReDim attributes2(40 To 42)

' open file and print the header '
Open "C:\temp\output.xml" For Output As #1
Print #1, "<ROOT>"
Print #1, "  <RANDOM>"

' iterate each row '
For r = 1 To UBound(data)

  ' iterate each column '
  For c = 1 To UBound(data, 2)

    ' build each attribute '
    attr = headers(1, c) & "=""" & data(r, c) & """"
    If c <= 39 Then
      attributes1(c) = attr
    Else
      attributes2(c) = attr
    End If
  Next

  ' print the row '
  Print #1, "    <RANDOM1 " & Join(attributes1, " ") & " >"
  Print #1, "      <COOKIE>"
  Print #1, "        <ORANGE " & Join(attributes2, " ") & " />"
  Print #1, "      </COOKIE>"
  Print #1, "    </RANDOM1>"

Next

' print the footer and close '
Print #1, "  </RANDOM>"
Print #1, "</ROOT>"
Close #1