Specific node in XML file use VBA

Question

I want to replace data in a XML file using VBA. The XML data looks like this:

<MarkGrades>
  <Grade MarkGrade="0" Label="3OP" TestBins="1,-1">0</Grade>
  <Grade MarkGrade="2" Label="GOOD" TestBins="2">2</Grade <Grade>           
  <Grade MarkGrade="4" Label="FU" TestBins="31,3135,3136,3312,">4</Grade>
  <Grade MarkGrade="3" Label="PA" TestBins="4016,4022,4029">3</Grade>
  <Grade MarkGrade="5" Label="OS" TestBins="12,13,20012,20013">5</Grade>
</MarkGrades>

I want to replace data in node TestBins. If MarkGrade="4" or Label="FU" then replace data in TestBins from "2" to "something". But I can't seem to figure out how to specify that:

Dim doc As Object
Set doc = CreateObject("MSXML2.DOMDocument")
Dim isLoaded As Boolean
Dim i As Integer
Dim filePath As Variant
filePath = "C:\Users\xxxx\Desktop\Splitter\test.xml"
isLoaded = doc.Load(filePath)
i = 0

If isLoaded Then
    Dim Grade  As msxml2.IXMLDOMNodeList
    Set Grade = doc.getElementsByTagName("Grade")

    Dim attr As msxml2.IXMLDOMAttribute
    Dim node As msxml2.IXMLDOMElement
    For Each node In Grade
        For Each attr In node.Attributes
        If attr.Name = "TestBins" Then
            i = i + 1
            If i = 3 Then
                attr.Value = SplitterMark.TextBox3.Value
            ElseIf i = 4 Then
                attr.Value = SplitterMark.TextBox4.Value
            ElseIf i = 5 Then
                attr.Value = SplitterMark.TextBox5.Value

            End If
        End If
        Next attr
    Next node

I tried to count if found TestBins count +1

That is working good. But can use with only this file because other file it is not common. like this:

<MarkGrades>
  <Grade MarkGrade="5" Label="OS" TestBins="12,13,20012,20013">5</Grade>
  <Grade MarkGrade="0" Label="3OP" TestBins="1,-1">0</Grade>
  <Grade MarkGrade="4" Label="FU" TestBins="31,3135,3136,3312,">4</Grade>
  <Grade MarkGrade="2" Label="GOOD" TestBins="2">2</Grade <Grade>           
  <Grade MarkGrade="3" Label="PA" TestBins="4016,4022,4029">3</Grade>     
</MarkGrades>

Any ideas?

You can access the attributes of interest directly by name, instead of looping over them. E.g. TestBins = node.Attributes.getNamedItem("ID").Text You can then use those values to determine how to handle each node — Tim Williams
– Tim Williams, Commented Mar 22, 2019 at 5:09
Do they always appear together and populated i.e. MarkGrade="4" Label="FU" such that you really only need to test for one? — QHarr
– QHarr, Commented Mar 22, 2019 at 6:56
I need to replace the data in TestBins if MarkGrade="4" or Label="FU". — Mr. Chat
– Mr. Chat, Commented Mar 22, 2019 at 7:17

QHarr · Accepted Answer · 2019-03-22 08:30:50Z

1

You could use xpath to identify the appropriate nodes and setAttribute for the value setting. You need to correct your xml though.

Option Explicit

Public Sub test()
    Dim xmlDoc As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    With xmlDoc
        .validateOnParse = True
        .setProperty "SelectionLanguage", "XPath"
        .async = False

        If Not .Load("C:\Users\User\Desktop\Test.xml") Then
            Err.Raise .parseError.ErrorCode, , .parseError.reason
        End If
    End With
    Dim elem As Object

    For Each elem In xmlDoc.SelectNodes("//Grade[@MarkGrade='4' or @Label='FU' and @TestBins]")
        elem.setAttribute "TestBins", "Banana"
    Next

    Debug.Print xmlDoc.XML
End Sub

Corrected xml line:

<Grade MarkGrade="2" Label="GOOD" TestBins="2">2</Grade>

answered Mar 22, 2019 at 8:30

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Specific node in XML file use VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related