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

Question

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!

Added a hint to possible better redesign of pattern xml to my post below. — T.M.
– T.M., Commented Nov 22, 2021 at 9:08

CDP1802 · Accepted Answer · 2021-11-21 17:26:34Z

2

See CreateTextFile method of a TextStream Objects

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

answered Nov 21, 2021 at 17:26

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

2 Comments

Sonal Over a year ago

Thanks a ton @CDP1802, this works a treat. :)

T.M. Over a year ago

Nicely done 'vtd :-) - Fyi See my posted alternative continuing with xml methods @CDP1802

T.M. · Accepted Answer · 2021-11-22 14:18:37Z

2

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.

edited Nov 22, 2021 at 14:18

answered Nov 21, 2021 at 19:25

T.M.

10k3 gold badges40 silver badges61 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related