CODEWITHSUNDEEP
excelxmlvba
Sonal
Sonal

Reputation: 137

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

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)

enter image description here

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

Upvotes: 1

Views: 1262

Answers (2)

T.M.
T.M.

Reputation: 9948

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

Side note to posted code

It's worth mentioning that the somehow peculiar insertion of a starting <root> and closing </root> tag into the loading xml string via

    doc.LoadXML Replace(ts.readall, "<metadata>", "<root><metadata>", 1, 1) & "</root>"

is a only a workaround rebuilding a well-formed xml input thus avoiding

    Error `-1072896683 XML document must have a top level element.`

So imo you might consider changing your pattern files to include metadata not at top-level, but at a subsequent hierarchy level already in design to provide for a loadable, well-formed xml markup.

Upvotes: 2

CDP1802
CDP1802

Reputation: 16392

See CreateTextFile method of a TextStream Objects

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

Upvotes: 2

Related Questions