Use Excel to Read XML

Sub Button1_Click()

' clear data

J = Worksheets("Sheet1").UsedRange.Rows.Count + 1

Worksheets("Sheet1").Range(Cells(1, 1), Cells(J, 3)).Clear

 

' to create data

    Dim objDOM As Object

    Dim targetNode As Object

    Dim Clone As Object

    Dim Node As Object

    Dim ChartUnit As Object

    Dim targetNode2 As Object

    Dim Clone2 As Object

    Dim Node2 As Object

    Dim ChartUnit2 As Object

    Set objDOM = CreateObject("MSXML2.DOMDocument") 'UTF8

    'Set objDOM = CreateObject("MSXML.DOMDocument") 'Normal

    objDOM.async = False

    

    facility = "CFSW"

    fname = CStr(Worksheets("Sheet1").Cells(1, 6).Value)

    ret = objDOM.Load(fname)

    Dim objPageHeader As Object

    I = 1

    If ret Then

       Set targetNode = objDOM.DocumentElement.SelectSingleNode("//UserConfig/Facilities/Facility/Connectors").ChildNodes

       For Each Clone In targetNode

            Set ChartUnit = Clone.CloneNode(True)

            Set Node = ChartUnit.FirstChild

            If ChartUnit.HasChildNodes Then

                Connector = Node.Text

                Set targetNode2 = ChartUnit.ChildNodes.Item(11).ChildNodes.Item(0).ChildNodes.Item(10).ChildNodes

                For Each Clone2 In targetNode2

                  Set ChartUnit2 = Clone2.CloneNode(True)

                  Set Node2 = ChartUnit2.FirstChild

                  If ChartUnit2.HasChildNodes Then

                     Device = Node2.Text

                     Worksheets("Sheet1").Cells(I, 1).Value = facility

                     Worksheets("Sheet1").Cells(I, 2).Value = Connector

                     Worksheets("Sheet1").Cells(I, 3).Value = Device

                     I = I + 1

                  End If

                Next Clone2

            End If

       Next Clone

    End If

    

    J = Worksheets("Sheet1").UsedRange.Rows.Count + 1

    

    Columns("A:C").Select

    Range("C1").Activate

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(1, 2), Cells(J, 2)) _

        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(1, 3), Cells(J, 3)) _

        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort

        .SetRange Range(Cells(1, 1), Cells(J, 3))

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Rows("1:1").Select

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("A1").Select

    ActiveCell.FormulaR1C1 = "Facility"

    Range("B1").Select

    ActiveCell.FormulaR1C1 = "Connector"

    Range("C1").Select

    ActiveCell.FormulaR1C1 = "Device"

    Range("A1:C1").Select

    Selection.Font.Bold = True

    Range("E2:F2").Select

    Selection.Cut

    Range("E1").Select

    ActiveSheet.Paste

    Range("E4").Select

    

End Sub