XML Node Update Using VBA-CodePudding

I have XML Data like

<Main>
<sub>data</sub>
<sub2>data2<sub2>
<sub3>data3<sub3>
</main>

how can we update data with New data using VBA variable

Dim newdata
newdata = "New Data"

And Get XML LIKE

<Main>
<sub>New data</sub>
<sub2>data<sub2>
<sub3>data<sub3>
</main>

CodePudding user response：

Please try the next function. It uses RegEx:

Function UpdateNode(strText As String, strTag As String, strReplace As String) As String
     With CreateObject("VBScript.RegExp")
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "<" & strTag & ">(.*)</" & strTag & ">"
        UpdateNode = .Replace(strText, "<" & strTag & ">" & strReplace & "</" & strTag & ">")
    End With
End Function

Test it using the next Sub:

Sub testUpdateNode()
    Dim txt As String
    txt = "<Main> " & vbCrLf & _
            "   <sub>data</sub>" & vbCrLf & _
            "   <sub2>data2</sub2>" & vbCrLf & _
            "   <sub3>data3</sub3>" & vbCrLf & _
            "</main>"
    Debug.Print UpdateNode(txt, "sub", "New Data")
    Debug.Print UpdateNode(txt, "sub2", "New Data")
End Sub

Take care to have a correct XML ("sub2", "sub3" missing ending slash "/")...

If there are more occurrences of the tag to be updated, the next version can be used:

Function UpdateNodes(strText As String, strTag As String, strReplace As String) As String
     Dim M As Object, i As Long
     With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "<" & strTag & ">(.*)</" & strTag & ">"
        If .Test(strText) Then
            Set M = .Execute(strText)
            For i = 0 To M.count - 1
                strText = Replace(strText, M(i), "<" & strTag & ">" & strReplace & "</" & strTag & ">")
            Next i
        End If
    End With
    UpdateNodes = strText
End Function

And test it in the same way:

  Debug.Print UpdateNodes(txt, "sub2", "New Data")

To have a different you should modify the string to be processed, to make it having two such tags...

   txt = "<Main> " & vbCrLf & _
         "   <sub>data</sub>" & vbCrLf & _
         "   <sub2>data2</sub2>" & vbCrLf & _
         "   <sub2>data3</sub2>" & vbCrLf & _
         "</main>"