<orders>
    <order>
                    <orderinfo>
                        <orderid>143310630</orderid>
                        <status><![CDATA[pending]]></status>
                        <state><![CDATA[new]]></state>
                        <createdat>2017-05-08 19:22:21</createdat>
                        <billingname><![CDATA[dd Knudsen]]></billingname>
                        <billingfirstname><![CDATA[dd]]></billingfirstname>
                        <billinglastname><![CDATA[Knudsen]]></billinglastname>
                        <billingcity><![CDATA[Koebenhavn s]]></billingcity>
                        <billingstreet><![CDATA[ddgade 55 st tv]]></billingstreet>
                        <billingcountry><![CDATA[DK]]></billingcountry>
                        <billingphone><![CDATA[34433444]]></billingphone>
                        <billingpostcode><![CDATA[2300]]></billingpostcode><shippingname><![CDATA[Robin Knudsen]]></shippingname>
                                <shippingfirstname><![CDATA[dd]]></shippingfirstname>
                                <shippinglastname><![CDATA[Knudsen]]></shippinglastname>
                                <shippingcity><![CDATA[Koebenhavn s]]></shippingcity>
                                <shippingstreet><![CDATA[ddgade 55 st tv]]></shippingstreet>
                                <shippingphone><![CDATA[34433444]]></shippingphone>
                                <shippingpostcode><![CDATA[2300]]></shippingpostcode>
                                <shippingcountry><![CDATA[DK]]></shippingcountry><shippingmethod><![CDATA[flatrate_flatrate]]></shippingmethod>
                        <paymentmethod><![CDATA[checkmo]]></paymentmethod>
                        <paymentmethodtitle><![CDATA[Check / Money order]]></paymentmethodtitle>
                        <email><![CDATA[thomas@goodie22.dk]]></email>
                        <shippingdescription><![CDATA[Flat Rate - Fixed]]></shippingdescription>
                    </orderinfo>
                    <items><item>
                    <id><![CDATA[445]]></id>
                    <product_id><![CDATA[2]]></product_id>
                    <sku><![CDATA[0]]></sku>
                    <name><![CDATA[GoodieboxSept14]]></name>
                    <qty><![CDATA[1.0000]]></qty>
                    <price>139.0000</price>
                    <rowtotal>139.0000</rowtotal>
                        <taxamount>0.0000</taxamount>
                        <taxpercent>0.0000</taxpercent>
                    <originalprice>139.0000</originalprice>
                    <baseprice>139.0000</baseprice>
                    <baseoriginalprice>139.0000</baseoriginalprice>
                    <baserowtotal>139.0000</baserowtotal>
                    <priceincltax>139.0000</priceincltax>
                    <basepriceincltax>139.0000</basepriceincltax>
                    <rowtototalincltax>139.0000</rowtototalincltax>
                    <baserowtotoalincltax></baserowtotoalincltax>
                    <weight>1.0000</weight>
                    <rowweight>1.0000</rowweight>
                    <product_type>simple</product_type>
                    <parent_item_id></parent_item_id>
                    <product_options><![CDATA[a:1:{s:15:"info_buyRequest";a:2:{s:3:"qty";i:1;s:7:"options";a:0:{}}}]]></product_options>
                </item>
                    </items>
                    <basetotalinvoiced></basetotalinvoiced>
                    <basetaxinvoiced></basetaxinvoiced>
                    <baseshippinginvoiced></baseshippinginvoiced>
                    <basetotalrefunded></basetotalrefunded>
                    <basetaxrefunded></basetaxrefunded>
                    <baseshippingrefunded></baseshippingrefunded>
                    <basetoglobalrate>1.0000</basetoglobalrate>
                    <discountamount>0.0000</discountamount>
                    <shippingamount>5.0000</shippingamount>
                    <grandtotal>144.0000</grandtotal>
                    <basesubtotal>139.0000</basesubtotal>
                    <taxamount>0.0000</taxamount>
                    <basetaxamount>0.0000</basetaxamount>
                    <basegrandtotal>144.0000</basegrandtotal>
                    <totalpaid></totalpaid>
                    <baseshippingtaxamount>0.0000</baseshippingtaxamount>

                <weight>1.0000</weight>
                <subtotal>139.0000</subtotal>
                    <basesubtotalincltax>139.0000</basesubtotalincltax>
                    <totalitemcount>1</totalitemcount>
                    <totalqtyordered>1.0000</totalqtyordered>
                    <shippingincltax>5.0000</shippingincltax>
                    <subtotalincltax>139.0000</subtotalincltax>
                    <baseshippingincltax>5.0000</baseshippingincltax>
                    <basetotalpaid></basetotalpaid><comments><item><entityid>45861</entityid><parentid>446</parentid><iscustomernotified>0</iscustomernotified><isvisibleonfront>0</isvisibleonfront><comment><![CDATA[]]></comment><status><![CDATA[pending]]></status><createdat><![CDATA[2017-05-08 19:22:21]]></createdat><entityname><![CDATA[order]]></entityname></item></comments></order>

</orders>

Sub MakeXML()
' create an XML file from an Excel table
Dim MyRow As Integer, MyCol As Integer, Temp As String, YesNo As Variant, DefFolder As String
Dim XMLFileName As String, XMLRecSetName As String, MyLF As String, RTC1 As Integer
Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String
sheetname = ActiveSheet.Name
Set ws = ThisWorkbook.Worksheets(sheetname)
MyLF = Chr(10) & Chr(13)    ' a line feed command
DefFolder = Application.ActiveWorkbook.Path '"C:"   'change this to the location of saved XML files

'MsgBox DefFolder

XMLFileName = "data_export.xml"  'FillSpaces(InputBox("1. Enter the name of the XML file:", "MakeXML CiM", "xl_xml_data"))
If Right(XMLFileName, 4) <> ".xml" Then
 XMLFileName = XMLFileName & ".xml"
End If

XMLRecSetName = "orderinfo" 'FillSpaces(InputBox("2. Enter an identifying name of a record:", "MakeXML CiM", "orderinfo"))
'MsgBox Cells(1, Columns.Count).End(xlToLeft).Column
RangeOne = "A1:N1" 'InputBox("3. Enter the range of cells containing the field names (or column titles):", "MakeXML CiM", "A3:D3")

MyRow = MyRng(RangeOne, 1)
For MyCol = MyRng(RangeOne, 3) To MyRng(RangeOne, 4)
 If Len(Cells(MyRow, MyCol).Value) = 0 Then
  MsgBox "Error: names range contains blank cell" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
 End If
 FldName(MyCol - MyRng(RangeOne, 3)) = FillSpaces(Cells(MyRow, MyCol).Value)
Next MyCol

RangeTwo = "A2:N3" 'InputBox("4. Enter the range of cells containing the data table:", "MakeXML CiM", "A4:D8")

If MyRng(RangeOne, 4) - MyRng(RangeOne, 3) <> MyRng(RangeTwo, 4) - MyRng(RangeTwo, 3) Then
  MsgBox "Error: number of field names <> data columns" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
End If
RTC1 = MyRng(RangeTwo, 3)

If InStr(1, XMLFileName, ":") = 0 Then
 XMLFileName = DefFolder & XMLFileName
End If

Open XMLFileName For Output As #1
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & "?>"
Print #1, "<orders>"

For MyRow = MyRng(RangeTwo, 1) To MyRng(RangeTwo, 2)
Print #1, "<" & XMLRecSetName & ">"
  For MyCol = RTC1 To MyRng(RangeTwo, 4)
  ' the next line uses the FormChk function to format dates and numbers
   'Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(FormChk(MyRow, MyCol)) & "</" & FldName(MyCol - RTC1) & ">"
  ' the next line does not apply any formatting
  Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & "</" & FldName(MyCol - RTC1) & ">"
    Next MyCol
 Print #1, "</" & XMLRecSetName & ">"

Next MyRow
Print #1, "</orders>"
Close #1
MsgBox XMLFileName & " created." & MyLF & "Process finished", vbOKOnly + vbInformation, "MakeXML CiM"
Debug.Print XMLFileName & " saved"
End Sub
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
Function MyRng(MyRangeAsText As String, MyItem As Integer) As Integer
' analyse a range, where MyItem represents 1=TR, 2=BR, 3=LHC, 4=RHC

Dim UserRange As Range
Set UserRange = Range(MyRangeAsText)
Select Case MyItem
 Case 1
 MyRng = UserRange.Row
 Case 2
 MyRng = UserRange.Row + UserRange.Rows.Count - 1
 Case 3
 MyRng = UserRange.Column
 Case 4
 MyRng = UserRange.Columns(UserRange.Columns.Count).Column
End Select
Exit Function

End Function
Function FillSpaces(AnyStr As String) As String
' remove any spaces and replace with underscore character
Dim MyPos As Integer
MyPos = InStr(1, AnyStr, " ")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "_"
 MyPos = InStr(1, AnyStr, " ")
Loop
FillSpaces = LCase(AnyStr)
End Function

Function FormChk(RowNum As Integer, ColNum As Integer) As String
' formats numeric and date cell values to comma 000's and DD MMM YY
FormChk = Cells(RowNum, ColNum).Value
If IsNumeric(Cells(RowNum, ColNum).Value) Then
 FormChk = Format(Cells(RowNum, ColNum).Value, "#,##0 ;(#,##0)")
End If
If IsDate(Cells(RowNum, ColNum).Value) Then
 FormChk = Format(Cells(RowNum, ColNum).Value, "dd mmm yy")
End If
End Function

Function RemoveAmpersands(AnyStr As String) As String
Dim MyPos As Integer
' replace Ampersands (&) with plus symbols (+)

MyPos = InStr(1, AnyStr, "&")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "+"
 MyPos = InStr(1, AnyStr, "&")
Loop
RemoveAmpersands = AnyStr
End Function