CODEWITHSUNDEEP
excelxmlvba
Mr. Chat
Mr. Chat

Reputation: 35

Specific node in XML file use VBA

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?

Upvotes: 1

Views: 431

Answers (1)

QHarr
QHarr

Reputation: 84465

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>

Upvotes: 1

Related Questions