Import XML workbook data

[FryGirl]

I have multiple .xml files in a folder and I need to place them in an Excel worksheet one on top of the other.

So far with the code below, it works to extract the first file, but when trying to extract the next file, I get the error...

The operation cannot be completed because the XML table is bound to a different XML map.

Sub LoopThruFolder()
    Dim MyFile As String
    Dim MyDir As String
    Dim wb As Workbook
    Dim ScrLR As Long
    Dim Rng As Range
    Dim DestLR As Long
    
    Set wb = ThisWorkbook
    
    MyDir = ThisWorkbook.Path & Application.PathSeparator
    MyFile = Dir(MyDir & "*.xml")
    ChDir MyDir
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Do While MyFile <> ""
        If MyFile <> ThisWorkbook.Name Then
        DestLR = wb.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        wb.XmlImport URL: = MyFile, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("A" & DestLR)
        End If
        MyFile = Dir()
    Loop
    Application.DisplayAlerts = True
End Sub

I've searched on-line for some tips and tutorials, but getting a little lost. Any thoughts?

 

[John_w]

Untested, try replacing the XmlImport line with:

        If ThisWorkbook.XmlMaps.Count = 0 Then
            'Import first XML data file, creating a new XML mapping
            ThisWorkbook.XmlImport URL:=MyDir & MyFile, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("A" & DestLR)
        Else
            'Append subsequent XML data files to existing mapping - don't specify Destination
            ThisWorkbook.XmlImport URL:=MyDir & MyFile, ImportMap:=Nothing, Overwrite:=False
        End If