title: Creating each row of excel data into xml files using VBA Macro
body: <p>I am trying to create each row (Specific Columns) of Excel data into xml files (with tags) using VBA Macro. I am able to create the files but data is not populating into xml files. Please help me!!</p>
  
  <pre class="lang-vb prettyprint-override"><code>Option Explicit
  
  Private Sub SaveAs_XML()
  On Error GoTo ErrHandle    
      Dim doc As New MSXML2.DOMDocument60, xslDoc As New MSXML2.DOMDocument60, newDoc As New MSXML2.DOMDocument60
      Dim root As IXMLDOMElement, dataNode As IXMLDOMElement, lastnameNode As IXMLDOMElement, AgeNode As IXMLDOMElement
      Dim dataNameAttrib As IXMLDOMAttribute, Attrib As IXMLDOMAttribute
      Dim nameAttrib As IXMLDOMAttribute, lastnameAttrib As IXMLDOMAttribute, AgeAttrib As IXMLDOMAttribute
      Dim i As Long
      Dim Folder As String
      Dim WS_Src As Worksheet, rng As Range, C As Range, d As Range
      Dim fs, f, ts, s
      Dim XDoc
  
      Folder = "\C:\New folder\"
      Set WS_Src = ThisWorkbook.Worksheets("data")
      Set rng = WS_Src.Range("B1", WS_Src.Range("B" &amp; Rows.Count).End(xlUp))
      For Each C In rng
          Set fs = CreateObject("Scripting.FileSystemObject")
          fs.CreateTextFile Folder &amp; C.Value &amp; ".xml"
          Set f = fs.GetFile(Folder &amp; C.Value &amp; ".xml")
      Next
  
      Set XDoc = CreateObject("MSXML2.DOMDocument")
      ' DECLARE XML DOC OBJECT '
      Set root = doc.createElement("list")
      doc.appendChild root
  
      ' WRITE TO XML '
      For i = 2 To Sheets(1).UsedRange.Rows.Count 
              ' DATA NODE '
              Set dataNode = doc.createElement("data")
              root.appendChild dataNode
              ' NAME ATTRIBUTE '
              Set dataNameAttrib = doc.createAttribute("name")
              dataNameAttrib.Value = Range("B" &amp; i)
              dataNode.setAttributeNode dataNameAttrib
              ' LASTNAME ATTRIBUTE '
              Set lastnameAttrib = doc.createAttribute("lastname")
              lastnameAttrib.Value = Range("C" &amp; i)
              lastnameNode.setAttributeNode lastnameAttrib            
              ' AGE ATTRIBUTE '
              Set AgeAttrib = doc.createAttribute("age")
              AgeAttrib.Value = Range("E" &amp; i)
              AgeNode.setAttributeNode AgeAttrib           
  
      Next i
  
          ' PRETTY PRINT RAW OUTPUT '
          xslDoc.LoadXML "&lt;?xml version=" &amp; Chr(34) &amp; "1.0" &amp; Chr(34) &amp; "?&gt;" _
              &amp; "&lt;xsl:stylesheet version=" &amp; Chr(34) &amp; "1.0" &amp; Chr(34) _
              &amp; "                xmlns:xsl=" &amp; Chr(34) &amp; "http://www.w3.org/1999/XSL/Transform" &amp; Chr(34) &amp; "&gt;" _
              &amp; "&lt;xsl:strip-space elements=" &amp; Chr(34) &amp; "*" &amp; Chr(34) &amp; " /&gt;" _
              &amp; "&lt;xsl:output method=" &amp; Chr(34) &amp; "xml" &amp; Chr(34) &amp; " indent=" &amp; Chr(34) &amp; "yes" &amp; Chr(34) &amp; "" _
              &amp; "            encoding=" &amp; Chr(34) &amp; "UTF-8" &amp; Chr(34) &amp; "/&gt;" _
              &amp; " &lt;xsl:template match=" &amp; Chr(34) &amp; "node() | @*" &amp; Chr(34) &amp; "&gt;" _
              &amp; "  &lt;xsl:copy&gt;" _
              &amp; "   &lt;xsl:apply-templates select=" &amp; Chr(34) &amp; "node() | @*" &amp; Chr(34) &amp; " /&gt;" _
              &amp; "  &lt;/xsl:copy&gt;" _
              &amp; " &lt;/xsl:template&gt;" _
              &amp; "&lt;/xsl:stylesheet&gt;"
  
          xslDoc.async = False  
  
  
      MsgBox "Successfully exported Excel data to XML!", vbInformation
      Exit Sub
  
  ErrHandle:
      MsgBox Err.Number &amp; " - " &amp; Err.Description, vbCritical
      Exit Sub
  End Sub
  </code></pre>
  
  <p>I want output to be something like this (xml file) for each row </p>
  
  <pre><code>$ Output
  
  &lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;List&gt;
      &lt;Data name="test1" 
       lastname="lastname1" 
       age ="24"
      /&gt;        
  &lt;/List&gt;
  </code></pre>