Excel VBA : How to parse an xml file and write in data then save it

[Aymen]

I am working on this since many days and am loosing much time on something supposed to be much more easier. I am very new and beginner on VB, VBA and XML..


am working on something like following:


1 - AN execl sheet with some cells where end user is asked to enter needed values; done
2 - Code that read values entered in these cells by end users;
3 - load an xml file; done
4 - parse it and then write the values got from excel cells into xml file (following a rule) than saving it; I am blocked here!
5 - then start my application (an other script will use the xml file values later).


I will simplify as much as possible:


the excel fille will be like following


CellA CellB
1 T1 V1
2 T2 V2
3 T3 V3
4 T4 V4
5 T5 V5
6 T6 V6


T refering to title.
the end user will enter the Values V1, V2, ... V6




The xml file is like following:

<environment><!--?xml version="1.0" encoding="UTF-8" standalone="no"?-->
<environment>
</environment></environment><?xml version="1.0" encoding="UTF-8" standalone="no"?><Environment>
<Variable>
<Name></Name>
<Caption>T1</Caption>
<Type>TEXT</Type>
<Value>V1</Value>
<Description></Description>
</Variable>
<Variable>
<Name></Name>
<Caption>T2</Caption>
<Type>TEXT</Type>
<Value>V2</Value>
<Description></Description>
</Variable>
<Variable>
<Name></Name>
<Caption>T3</Caption>
<Type>TEXT</Type>
<Value>V3</Value>
<Description></Description>
</Variable> <Variable>
<Name></Name>
<Caption>T4</Caption>
<Type>TEXT</Type>
<Value>V4</Value>
<Description></Description>
</Variable> <Variable>
<Name></Name>
<Caption>T5</Caption>
<Type>TEXT</Type>
<Value>V5</Value>
<Description></Description>
</Variable>
</Variable> <Variable>
<Name></Name>
<Caption>T6</Caption>
<Type>TEXT</Type>
<Value>V6</Value>
<Description></Description>
</Variable>
</Environment>

as you see i need to parse this file and enter values (V1....V6) into <value> with reference to for each one.


below my VBA code until the line where i am blocked:


'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


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 & "\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 & "\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 & "\Environment\EnvVar.xml")





i really appreciate a help on this.
Thanks.</value>