Microsoft 365 Manipuler les données XML à partir d'Excel

Sub LireVddDiagProjects()

    Dim xml As Object                ' MSXML2.DOMDocument60
    Dim nodes As Object              ' IXMLDOMNodeList
    Dim node As Object               ' IXMLDOMNode
    Dim f As Worksheet
    Dim ligne As Long

    '--- Feuille de sortie
    Set f = ThisWorkbook.Sheets("Feuil2")
    f.Cells.Clear
    f.Range("A1:D1").Value = Array("ID OR", "Description", "Catégorie", "Paramètres")
    ligne = 2

    '--- Création du DOM (late binding)
    Set xml = CreateObject("MSXML2.DOMDocument.6.0")
    xml.async = False
    xml.validateOnParse = False

    '--- Charger le fichier XML
    If Not xml.Load("D:\Temp\VddDiag.xml") Then
        MsgBox "Erreur de chargement du XML"
        Exit Sub
    End If

    '--- Déclarer le namespace (méthode correcte MSXML)
    xml.SetProperty "SelectionNamespaces", _
        "xmlns:ns='http://example.com/vdddiag/projects'"   ' <-- adapte l’URL

    '--- Récupérer tous les nœuds OR
    Set nodes = xml.SelectNodes("//ns:OR")

    If nodes.Length = 0 Then
        MsgBox "Aucun nœud OR trouvé. Vérifie le namespace."
        Exit Sub
    End If

    '--- Parcours des OR
    For Each node In nodes

        Dim idOR As String
        Dim desc As String
        Dim cat As String
        Dim params As String

        ' ID
        idOR = node.Attributes.getNamedItem("id").Text

        ' Description
        On Error Resume Next
        desc = node.SelectSingleNode("ns:Description").Text
        On Error GoTo 0

        ' Catégorie
        On Error Resume Next
        cat = node.SelectSingleNode("ns:Category").Text
        On Error GoTo 0

        ' Paramètres
        params = LireParametres_Late(node)

        '--- Écriture dans Excel
        f.Cells(ligne, 1).Value = idOR
        f.Cells(ligne, 2).Value = desc
        f.Cells(ligne, 3).Value = cat
        f.Cells(ligne, 4).Value = params

        ligne = ligne + 1

    Next node

    MsgBox "Lecture terminée"

End Sub

'----------------------------------------------------------
' Fonction : lit tous les paramètres d’un OR (late binding)
'----------------------------------------------------------
Function LireParametres_Late(n As Object) As String

    Dim pList As Object
    Dim p As Object
    Dim txt As String

    Set pList = n.SelectNodes("ns:Parameters/ns:Parameter")

    For Each p In pList
        txt = txt & p.Attributes.getNamedItem("name").Text & "=" & p.Text & "; "
    Next p

    LireParametres_Late = txt

End Function

<?xml version="1.0" encoding="UTF-8"?>
<VddDiagProjects
    xmlns="http://example.com/vdddiag/projects"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://example.com/vdddiag/projects
        http://example.com/vdddiag/projects/schema.xsd">

    <VddEcu name="ECU_Moteur" version="1.2.4">

        <OR id="OR-12345">
            <Description>Lecture des défauts moteur</Description>
            <Category>Diagnostic</Category>
            <Parameters>
                <Parameter name="Timeout" unit="ms">500</Parameter>
                <Parameter name="Retries">3</Parameter>
            </Parameters>
        </OR>

        <OR id="OR-67890">
            <Description>Effacement des défauts</Description>
            <Category>Maintenance</Category>
            <Parameters>
                <Parameter name="SecurityLevel">2</Parameter>
            </Parameters>
        </OR>

        <OR id="OR-44556">
            <Description>Lecture des valeurs en temps réel</Description>
            <Category>Mesure</Category>
            <Parameters>
                <Parameter name="SamplingRate" unit="Hz">10</Parameter>
            </Parameters>
        </OR>

    </VddEcu>

</VddDiagProjects>