I am working on something like the following:
I will simplify as much as possible:
The Excel file will be like the following example...
CellA CellB
1 T1 V1
2 T2 V2
3 T3 V3
4 T4 V4
5 T5 V5
6 T6 V6
"T" refers to title. The end user will enter the Values V1, V2, ... V6
The XML file is structured like this:
<?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 can see I need to parse this file and enter values (V1....V6) into with reference to for each one.
Below is my VBA code until the line where I am stuck:
'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 & "\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")
Here is some code that can help you get started
Dim doc As DOMDocument
Set doc = New DOMDocument
doc.Load "C:\x.xml"
Dim Variables As IXMLDOMNodeList
Dim variable As IXMLDOMNode
Set Variables = doc.SelectNodes("/Environment/Variable")
For Each variable In Variables
Debug.Print variable.SelectNodes("Caption").Item(0).Text
Debug.Print variable.SelectNodes("Type").Item(0).Text
Next
To make it work, select Tools - References and select Microsoft XML v6.0. There are many ways to solve this, but XPath (the language used in SelectNodes) is very good to know in cases like this.