Import multiple XML files into Excel
Not knowing what your XMl files are like or what you're trying to extract from them, this is mostly speculative:

Generally, you want to strip away as much unwanted data as early as possible in the process. Rather than trying to import an entire file to the worksheet every time, why not strip your document down to a NodeList, and then either target specific nodes (if you're only after certain data) or iterate over them and extract the data values into an array. Then you can print that Array to a worksheet.

I recently built a Tool to extract data from corporate accounts. It loads an XML doc (typically 100kb), parses the Node Tree, searches for specific nodes using XPath, extracts the displayed text of each and sticks it in an array that's printed to a worksheet every 500 lines or so. It grabs 5 sets of nodes from each document, and parses about 1 Million documents/hour, or nearly 300 documents / second.

Below is an extract showing the basic processing of loading, stripping, searching and retrieving.

Public Sub GetDirectorsFromFile(ByVal strFullFilename As String)

    Dim xDoc As MSXML2.DOMDocument
    Set xDoc = New MSXML2.DOMDocument

    Dim colDirectors As Collection
    Set colDirectors = New Collection

    With xDoc

        If .Load(strFullFilename) Then

            .setProperty "SelectionLanguage", "XPath"
            GetDirectorsFromXml xDoc, colDirectors

        End If

    End With

End Sub

Public Sub GetDirectorsFromXml(ByRef xDoc As MSXML2.DOMDocument, ByRef colDirectors As Collection)

    Dim ixItem As Long
    Dim xPathSearchString As String
    Dim nodes As MSXML2.IXMLDOMNodeList
    Dim nodeText As String

        xPathSearchString = "//*[contains(@name,""NameEntityOfficer"")]"
        Set nodes = xDoc.SelectNodes(xPathSearchString )

        For ixItem = 0 To nodes.Length - 1
            nodeText = nodes.Item(ixItem).text
            colDirectors.Add text
        Next ixItem

End Sub