Modify and replace an XML file through a Macro to the same path (Excel VBA)-CodePudding

I have a custom-button in my excel sheet, and when the user clicks it, the code enables the user to upload a file, and then code modifies the uploaded file, and stores the modified contents in a String variable s. -

Option Explicit

Sub Button1_Click()
    Dim fso As Object, ts As Object, doc As Object
    Dim data As Object, filename As String
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' select file
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show <> -1 Then Exit Sub
        filename = .SelectedItems(1)
    End With
    
    ' read file and add top level
    Set doc = CreateObject("MSXML2.DOMDocument.6.0")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpentextFile(filename)
    doc.LoadXML Replace(ts.readall, "<metadata>", "<root><metadata>", 1, 1) & "</root>"
    ts.Close
    
    ' import data tag only
    Dim s As String
    Set data = doc.getElementsByTagName("data")(0)
    s = data.XML
    ' MsgBox s
    
    ' replace the original XML file with contents of variable s here
    
    If MsgBox(s & vbCrLf, vbYesNo) = vbYes Then
        Application.SendKeys ("%lt")
    Else
        MsgBox "Ok"
    End If
End Sub

Let's say I clicked the button and uploaded an XML file C:/My Folder/sample.xml. Now the code modifies it, and updates the file (with the new contents stored in variable s). Here's a representative image - (the modified contents is direct value of s variable)

How do I achieve the above? Kindly guide... Thanks!

CodePudding user response：

See CreateTextFile method of a TextStream Objects

Set ts = fso.CreateTextFile(filename, True)
ts.Write s
ts.Close

CodePudding user response：

Why not continue with XML methods by loading the wanted string again (after Set data = doc.getElementsByTagName("data")(0)):

    doc.LoadXML data.XML
    doc.Save filename