Macro Code need for Below Table & Expected xml file Bleow to be - Microsoft Community

id	inv-no	date	party	item name	qty	rate	amount	inv-amount	narration
10011	sail/07/2542	1-apr-2014	a	item 1	50	2400	120000	156000	large-1
10011				item 3	20	1800	36000
10022	ril/56895425	1-apr-2014	b	item 2	5	3100	15500	87500	small-1
10022				item 1	15	2400	36000
10022				item 3	20	1800	36000

output xml file following xml tag: 

<id>1011</id>
<inv-no>sail/07/2542</inv-no>
<date>20140401</date>
<party>a</party>
<item name>item 1</item name>
<qty>50</qty>
<rate>2400</rate>
<amount>120000</amount>
<inv-amount>156000</inv-amount>
<narration> large -1</narration>
<item name>item 3</item name>
<qty>20</qty>
<rate>1800</rate>
<amount>36000</amount>

<id>10022</id>
<inv-no>ril/568954125</inv-no>
<date>20140401</date>
<party>b</party>
<item name>item 2</item name>
<qty>5</qty>
<rate>3100</rate>
<amount>15500</amount>
<inv-amount>87500</inv-amount>
<narration>small-1</narration>
<item name>item 1</item name>
<qty>15</qty>
<rate>2400</rate>
<amount>36000</amount>
<item name>item 3</item name>
<qty>20</qty>
<rate>1800</rate>
<amount>36000</amount>

please provide sample macro code above table  

hi,

step1

fix headers (without blanks)

example

instead of 

item name

write

item-name

or

item_name

step2

data in active sheet

result

on desktop

file name = activesheetname.xml

run code....

[edit..]

sub convert_activesheet_to_xml()
'july 05, 2017
const spath string = "c:\users\username\desktop\" '<< target path/change needed
const  sstr string = ".xml"
dim ws worksheet
set ws = activesheet '<< data in active sheet
dim sname
sname = activesheet.name
dim r long, c long, long, j long
r = ws.[a1].currentregion.rows.count
c = ws.[a1].currentregion.columns.count
dim sfile string
sfile = spath & sname & sstr
'
'###################
'step1) delete <old> xml maps
dim objmap xmlmap
each objmap in activeworkbook.xmlmaps
objmap.delete
next objmap
'###################
'
'###################
'step2) create xml file via adodb
dim sdata string
application.calculation = xlcalculationmanual
dim obj object
set obj = createobject("adodb.stream")
obj.type = 2
obj.charset = "utf-8"
obj.open
sdata = "<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>"
obj.writetext sdata, 1
sdata = "<" & sname & " xmlns:xsi=""http://www.w3.org/2001/xmlschema-instance"">"
obj.writetext sdata, 1
= 2 r
sdata = "<row>"
obj.writetext sdata, 1
j = 1 c
sdata = "<" & ws.cells(1, j).value & ">" & ws.cells(i, j).value & "</" & ws.cells(1, j).value & ">"
obj.writetext sdata, 1
next
sdata = "</row>"
obj.writetext sdata, 1
next
sdata = "</" & sname & ">"
obj.writetext sdata
obj.savetofile sfile, 2
obj.close
set obj = nothing
application.calculation = xlcalculationautomatic
'###################
'
'###################
'step3) open xml file via notepad
dim npad
npad = shell("c:\windows\notepad.exe " & sfile, 1)
'###################
end sub

1) source data in sheet1

2) xml open via notepad

3) xml open via i.explorer

from diveloper tab

select insert add xml file in new sheet

select source mapping

pic

edit file

and select export

note

in column c

date: custom format d-mmm-yyyy

in xml returns d/m/yyyy

Office / Excel / Microsoft Office Programming / Office 2013