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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
238
Office Version
  1. 365
Platform
  1. Windows
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
XmlExample.png



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

VBA Code:
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:

CurrentDataOutput.PNG


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

DataOutputGoal.PNG


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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
238
Office Version
  1. 365
Platform
  1. Windows
Also, quick follow-on,

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

Forum statistics

Threads
1,148,140
Messages
5,745,035
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top