i have following xml import excel using vba
<rootelement xmlns:n0="http://www.w3.org/n0/" xmlns:n1="http://www.w3.org/n1/"> <n0:partner> <n1:identifier>emh38</n1:identifier> <n1:a> <n1:b> <n1:c>wzfr8</n1:c> <n1:d>coll</n1:d> <n1:e>1</n1:e> </n1:b> <n1:b> <n1:c>x3hv7</n1:c> <n1:d>coll</n1:d> <n1:e>2</n1:e> </n1:b> <n1:b> <n1:c>x5e86</n1:c> <n1:d>coll</n1:d> <n1:e>3</n1:e> </n1:b> <n1:b> <n1:c>x5fc6</n1:c> <n1:d>coll</n1:d> </n1:b> <n1:b> <n1:c>x5fl6</n1:c> <n1:d>coll</n1:d> <n1:e>5</n1:e> </n1:b> </n1:a> </n0:partner> after reading in file, output in excel worksheet was
identifier c d e emh38 wzfr8 coll 1 x3hv7 coll 2 x5e86 coll 3 x5fc6 coll x5fl6 coll 5 the preferred output after reading in file have following. how can make identifier appear on each row?
identifier c d e emh38 wzfr8 coll 1 emh38 x3hv7 coll 2 emh38 x5e86 coll 3 emh38 x5fc6 coll emh38 x5fl6 coll 5
here, approach problem.
public sub readxml() dim xmlurl string dim xmldoc new msxml2.domdocument dim partner, elements, bnode, child msxml2.ixmldomnode dim row integer 'get xml file path xmlurl = thisworkbook.path & "\test.xml" xmldoc.async = false 'if loading xml file has no error if xmldoc.load(xmlurl) 'set start row of sheet row = 1 'get root element xml document set elements = xmldoc.documentelement 'loop child tags "n0:partner" tags each partner in elements.childnodes 'loop child tags 'n1:a' tags each bnode in partner.childnodes(1).childnodes sheets("sheetname").range("a" & row) = partner.childnodes(0).text 'loop child tags from 'n1:b' tags each child in bnode.childnodes 'check node name , set value cell select case child.nodename case "n1:c" sheets("sheetname").range("c" & row) = child.text case "n1:d" sheets("sheetname").range("d" & row) = child.text case "n1:e" sheets("sheetname").range("e" & row) = child.text end select next child 'increase row row = row + 1 next bnode next partner end if end sub 
Comments
Post a Comment