VBA to Import Multiple XML Files with FileDialog into an Excel table - more than one level of nodes

[dougmarkham]

Hi Folks,

This is my first go out extracting *.xml order data into an excel table.

Aim: to select a folder in FileDialog, and select/read multiple xml files to extract the order data into an excel table.

Basic sales order structure in one xml file:
Order 1
-----Order 1 details (customer number, delivery address, warehouse, courier service, collection date, etc)
~~~~~~~Order line 1 (product, pack size, order quantity etc)
~~~~~~~Order line 2 (product, pack size, order quantity etc)
~~~~~~~Order line n... (product, pack size, order quantity etc)
Order 2
-----Order 2 details (customer number, delivery address, warehouse, courier service, collection date, etc)
~~~~~~~Order line 1 (product, pack size, order quantity etc)
~~~~~~~Order line 2 (product, pack size, order quantity etc)
~~~~~~~Order line n... (product, pack size, order quantity etc)
Order 3
-----Order 3 details (customer number, delivery address, warehouse, courier service, collection date, etc)
~~~~~~~Order line 1 (product, pack size, order quantity etc)
~~~~~~~Order line 2 (product, pack size, order quantity etc)
~~~~~~~Order line n... (product, pack size, order quantity etc)
etc

Here is an example xml tree

Currently, with the following VBA within my sheet1, I am able to extract only one child-node:

Private Sub CommandButtonImport_Click()

Dim fd As Office.FileDialog: Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim xdoc As Object: Set xdoc = CreateObject("MSXML2.DOMDocument")
With fd
    .Filters.Clear
    .Title = "Select Multiple XML Files"
    .Filters.Add "XML File", "*.xml", 1
    .AllowMultiSelect = True
    
    If .Show = True Then
        xdoc.async = False: xdoc.validateOnParse = False
        row_number = 1
        For i = 1 To .SelectedItems.Count
            xmlFileName = fd.SelectedItems(i)
            xdoc.Load (xmlFileName)
            Set Products = xdoc.DocumentElement
            For Each Product In Products.ChildNodes
                Application.Range("ProductsRange").Cells(row_number, 1).Value = Product.ChildNodes(0).Text
                Application.Range("ProductsRange").Cells(row_number, 2).Value = Product.ChildNodes(1).Text
                Application.Range("ProductsRange").Cells(row_number, 3).Value = Product.ChildNodes(2).Text
                Application.Range("ProductsRange").Cells(row_number, 4).Value = Product.ChildNodes(3).Text
                Application.Range("ProductsRange").Cells(row_number, 5).Value = Product.ChildNodes(4).Text
                Application.Range("ProductsRange").Cells(row_number, 6).Value = Product.ChildNodes(5).Text
                Application.Range("ProductsRange").Cells(row_number, 7).Value = Product.ChildNodes(6).Text
                Application.Range("ProductsRange").Cells(row_number, 8).Value = Product.ChildNodes(7).Text
                Application.Range("ProductsRange").Cells(row_number, 9).Value = Product.ChildNodes(8).Text
                Application.Range("ProductsRange").Cells(row_number, 10).Value = Product.ChildNodes(9).Text
                Application.Range("ProductsRange").Cells(row_number, 11).Value = Product.ChildNodes(10).Text
                row_number = row_number + 1
            Next Product
            
        Next i
    End If
End With

End Sub

It comes out like this:

I'm aiming to extract the data into this format:

I have Googled and looked through a multitude of related forum posts and not yet found an analogous question, and am unfamiliar with this syntax.

Would anyone be willing to show me how it's possible to go one node further i.e.,
a) Add each sales order line into the blue table whilst...
b) Leaving blank lines in the green table until the next sales order?

Kind regards,

Doug.

 

[dougmarkham]

Also, quick follow-on,

How would one modify the code to include the filename in column A, in line with each sales order line?