transforming an excel sheet into XML-file

[keeper85]

Hi I have an excell sheet where the range("a1:aa7") is covered with data. The range("a1:aa1") always has the title of the column indicated. What I want to do with this data is to create an xml file out of it with a defined mapping. I have found the following code online, however, it is not the outcome I am looking for.

Sub ExporttoXML()
'dim ws as worksheet
    Dim filename As Variant
    Dim TDOpenTag As String, TDCloseTag As String
    Dim cellcontents As String
    Dim rng As Range
    Dim r As Long, c As Integer
 
'set the range
    Set rng = Range("a1:aa6")
'get a file name
    filename = Application.GetSaveAsFilename(InitialFileName:="myrange.xml", _
    filefilter:="XML Files(*.xml),*.xml")
    If filename = False Then Exit Sub
'open the text file
    Open filename For Output As #1
 
'write the  tag
Print #1, ""
Print #1, ""
'loop through the cells
For r = 2 To rng.Rows.Count
    Print #1, ""
    For c = 1 To rng.Columns.Count
        Print #1, "";
        If IsDate(rng.Cells(r, c)) Then
            Print #1, Format(rng.Cells(r, c), "yyyy-mm-dd");
        Else
            Print #1, rng.Cells(r, c).Text;
        End If
        Print #1, ""
    Next c
    Print #1, ""
Next r
'close the table
Print #1, ""
'close the file
Close #1
'tell the user
MsgBox rng.Rows.Count - 1 & "record were exported to" & filename
 
End Sub

I am trying to create an XML file that looks like this:

4.00
20010514

20010514
1
final

999
A97542
S
1
N/A
1
USD
10325.56
-5692.52
22555.66

CME

SP
1
USD
0

CME
SP
FUT
200106
5


CME
SP
FUT
200112
-22


CME
SP
FUT
200206
17


CME
SP
OOF
200106
200106
C
800
322


CME
SP
OOF
200106
200106
P
875
-567

Can anyone help me with this or tell me where I can find further information on this topic?