Need to merge two xml files using Excel vba

If you have lots of files you could put them in a folder then use cmd to combine them into one file (assuming they don't have root nodes). Then use fileSystemObject to add the root node. I decided to work with the existing document though I thought about using .appendChild and .createElement to add the root node with an additional document variable. Think I might actually prefer that.

Option Explicit

Public Sub CombineFiles()
    Dim cmd As String, fso As Object, xmlDoc As Object, numberOfFilesInFolder As Long, folder As Object
    Const FOLDER_PATH As String = "C:\Users\User\Desktop\XML Test"
    Const COMBINED_FILE_PATH As String = "C:\Users\User\Desktop\XML Test\Combined.xml"

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(FOLDER_PATH)

    numberOfFilesInFolder = folder.Files.Count
    cmd = "cmd /c cd """ & folder & """ && copy *.xml Combined.xml"

    Shell cmd, vbNormalFocus

    Do
        DoEvents
    Loop Until folder.Files.Count = numberOfFilesInFolder + 1

    AddRootNode COMBINED_FILE_PATH, fso
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")

    With xmlDoc
        .validateOnParse = True
        .setProperty "SelectionLanguage", "XPath"
        .async = False
        If Not .Load(COMBINED_FILE_PATH) Then
            Err.Raise .parseError.ErrorCode, , .parseError.reason
            Exit Sub
        End If
    End With
End Sub
Public Sub AddRootNode(ByVal filepath As String, fso As Object)
    Const READING = 1
    Const WRITING = 2
    Dim file As Object, contents As String

    Set file = fso.OpenTextFile(filepath, READING)

    contents = file.ReadAll
    file.Close
    contents = "<Union>" & vbCrLf & Replace$(contents, Chr$(26), vbNullString) & vbCrLf & "</Union>"
    Set file = fso.OpenTextFile(filepath, WRITING, True)
    file.Write contents
    file.Close
End Sub

References:

1. https://www.tek-tips.com/viewthread.cfm?qid=1421842
2. https://en.wikipedia.org/wiki/Substitute_character
3. Combining multiple xml documents into one large one with a batch file @Bhaskar
4. Create xml rootNode via c#