Reputation: 137
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!
Upvotes: 1
Views: 1262
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
Reputation: 16392
See CreateTextFile method of a TextStream Objects
Set ts = fso.CreateTextFile(filename, True)
ts.Write s
ts.Close
Upvotes: 2