title: How to parse an XML file and write in data then save it
body: <p>I am working on something like the following:</p>
  
  <ol>
  <li>An Excel sheet with some cells where the end user is asked to enter
  required values;  done </li>
  <li>Code that read values entered in these cells
  by end users;  </li>
  <li>Load an XML file; done </li>
  <li>Parse it and then write the values retrieved from Excel cells into XML file (following a rule) then save it;  I am stuck here! </li>
  <li>Start my application (another script will use the XML file values later).</li>
  </ol>
  
  <p>I will simplify as much as possible:</p>
  
  <p>The Excel file will be like the following example...</p>
  
  <pre><code>   CellA     CellB
  1  T1        V1
  2  T2        V2
  3  T3        V3
  4  T4        V4
  5  T5        V5
  6  T6        V6
  </code></pre>
  
  <p>"T" refers to title. The end user will enter the Values V1, V2, ... V6</p>
  
  <p>The XML file is structured like this:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="no"?&gt;
  &lt;Environment&gt;
      &lt;Variable&gt;
          &lt;Name&gt;&lt;/Name&gt;
          &lt;Caption&gt;T1&lt;/Caption&gt;
          &lt;Type&gt;TEXT&lt;/Type&gt;
          &lt;Value&gt;V1&lt;/Value&gt;
          &lt;Description&gt;&lt;/Description&gt;
      &lt;/Variable&gt;
          &lt;Variable&gt;
          &lt;Name&gt;&lt;/Name&gt;
          &lt;Caption&gt;T2&lt;/Caption&gt;
          &lt;Type&gt;TEXT&lt;/Type&gt;
          &lt;Value&gt;V2&lt;/Value&gt;
          &lt;Description&gt;&lt;/Description&gt;
      &lt;/Variable&gt;
          &lt;Variable&gt;
          &lt;Name&gt;&lt;/Name&gt;
          &lt;Caption&gt;T3&lt;/Caption&gt;
          &lt;Type&gt;TEXT&lt;/Type&gt;
          &lt;Value&gt;V3&lt;/Value&gt;
          &lt;Description&gt;&lt;/Description&gt;
      &lt;/Variable&gt; &lt;Variable&gt;
          &lt;Name&gt;&lt;/Name&gt;
          &lt;Caption&gt;T4&lt;/Caption&gt;
          &lt;Type&gt;TEXT&lt;/Type&gt;
          &lt;Value&gt;V4&lt;/Value&gt;
          &lt;Description&gt;&lt;/Description&gt;
      &lt;/Variable&gt; &lt;Variable&gt;
          &lt;Name&gt;&lt;/Name&gt;
          &lt;Caption&gt;T5&lt;/Caption&gt;
          &lt;Type&gt;TEXT&lt;/Type&gt;
          &lt;Value&gt;V5&lt;/Value&gt;
          &lt;Description&gt;&lt;/Description&gt;
      &lt;/Variable&gt;
      &lt;/Variable&gt; &lt;Variable&gt;
          &lt;Name&gt;&lt;/Name&gt;
          &lt;Caption&gt;T6&lt;/Caption&gt;
          &lt;Type&gt;TEXT&lt;/Type&gt;
          &lt;Value&gt;V6&lt;/Value&gt;
          &lt;Description&gt;&lt;/Description&gt;
      &lt;/Variable&gt;
  &lt;/Environment&gt;
  </code></pre>
  
  <p>As you can see I need to parse this file and enter values (V1....V6) into  with reference to  for each one.</p>
  
  <p>Below is my VBA code until the line where I am stuck:</p>
  
  <pre><code>'Option Explicit
  Private Sub RunTest_Click()
  
  Dim envFrmwrkPath As String
  Dim ApplicationName As String
  Dim TestIterationName, ServerIp, Login, Password, TraderLiteLogPath  As String
  Dim objfso, app, Eval As Object
  Dim i, Msgarea`enter code here`
  
  Dim EnvVarXML As MSXML2.DOMDocument60
  
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  '''these are added when trying to find a way to parse the xml --- you can change them
  Dim Variable As MSXML2.IXMLDOMNode
  'Dim oAttributes As MSXML.IXMLDOMNamedNodeMap
  Dim ORoot As MSXML2.IXMLDOMNode
  Dim objUIElement As MSXML2.IXMLDOMElement
  Dim OChildren As MSXML2.IXMLDOMNodeList
  Dim OChild As MSXML2.IXMLDOMNode
  Dim OVariable As MSXML2.IXMLDOMNode
  Dim OAttributes As MSXML2.IXMLDOMNamedNodeMap
  'Dim objUIElement As Object
  Dim field As Object
  ''''''''''''''''''''''''''''''''''''''''''''''''''''
  
  
  'load Env Variables from Excel
  
  ApplicationName = ActiveSheet.Range("E4").Value
  envFrmwrkPath = ActiveSheet.Range("E6").Value
  TestIterationName = ActiveSheet.Range("E8").Value
  ServerIp = ActiveSheet.Range("E10").Value
  Login = ActiveSheet.Range("E12").Value
  Password = ActiveSheet.Range("E14").Value
  TraderLiteLogPath = ActiveSheet.Range("E16").Value
  
  'load xml file
  Set objParser = CreateObject("Microsoft.XMLDOM")
  Set EnvVarXML = New MSXML2.DOMDocument60
  
  
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ''test_load
  '''''''''''''''-----------------------------------------------------
  'Set EnvVarXML = CreateObject("Microsoft.XMLDOM")
  'Set EnvVarXML = New MSXML2.DOMDocument60
   'If EnvVarXML.Load(envFrmwrkPath &amp; "\Environment\EnvVar.xml") Then
    ' for debug only
    'MsgBox "file loaded correctly", vbOKOnly
    ' for debug only
   'Else
   ' for debug only
   'MsgBox "file not loaded", vbcrtical
   ' for debug only
    'End If
  '''''''''''''''-----------------------------------------------------
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
  'load xml file
  EnvVarXML.Load (envFrmwrkPath &amp; "\Environment\EnvVar.xml")
  
   'parse file and change values
   '''the following may have no sense for an experiment one of you
   Set ORoot = EnvVarXML.DocumentElement
   For Each OVariable In ORoot.ChildNodes
     Set OAttributes = OVariable.Attributes
     Set OChildren = OVariable.ChildNodes
      '''deleted many lines as found no way ''''''' 
         Set EnvVarXML = Nothing
   Next
      EnvVarXML.Save (envFrmwrkPath &amp; "\Environment\EnvVar.xml")
  </code></pre>