Mapper les éléments d'un XML sur une feuille via VBA

**Nono02P** · 06/06/2018, 16h26

Bonjour,

Je bute sans trouver de solution à mon problème depuis plusieurs heures donc je fini par poster ici, je m'excuse par avance si ce sujet a pu déjà être abordé par le passé mais je n'ai rien trouvé.

Voilà, j'ai un fichier XML qui ressemble à ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?xml version="1.0" encoding="UTF-8"?>
<Logics version="1000" productid="93" productrevision="1" softversion="4591" options="1,2,3,4,5,12,13,17,22,24,27,32,48,58,60,61,62,63,93,108,110" hardware="4,6,8,22,29,39,61,66,76,77,78,81,82,83,84,85,86">
  <Logic name="Logic 1" offset="1" eventa="0" eventb="0" eventc="0" operators="21" relayout="0" relaydelay="4" collapsed="false" description="test"/>
  <Logic name="Logic 2" offset="7" eventa="0" eventb="0" eventc="0" operators="4" relayout="0" relaydelay="0" collapsed="true"/>
  <Logic name="Logic 3" offset="13" eventa="0" eventb="0" eventc="0" operators="16" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 4" offset="19" eventa="0" eventb="0" eventc="0" operators="2" relayout="192" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 5" offset="25" eventa="0" eventb="0" eventc="0" operators="8" relayout="193" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 6" offset="31" eventa="0" eventb="0" eventc="0" operators="32" relayout="194" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 7" offset="37" eventa="0" eventb="0" eventc="0" operators="10" relayout="559" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 8" offset="43" eventa="0" eventb="0" eventc="0" operators="40" relayout="311" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 9" offset="49" eventa="0" eventb="0" eventc="0" operators="34" relayout="312" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 10" offset="55" eventa="0" eventb="0" eventc="0" operators="42" relayout="434" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 11" offset="61" eventa="0" eventb="0" eventc="0" operators="22" relayout="509" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 12" offset="67" eventa="0" eventb="0" eventc="0" operators="28" relayout="510" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 13" offset="73" eventa="0" eventb="0" eventc="0" operators="52" relayout="510" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 14" offset="79" eventa="0" eventb="0" eventc="0" operators="30" relayout="510" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 15" offset="85" eventa="0" eventb="0" eventc="0" operators="60" relayout="510" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 16" offset="91" eventa="0" eventb="0" eventc="0" operators="62" relayout="510" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 17" offset="97" eventa="0" eventb="0" eventc="0" operators="0" relayout="515" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 18" offset="103" eventa="0" eventb="0" eventc="0" operators="0" relayout="516" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 19" offset="109" eventa="0" eventb="0" eventc="0" operators="0" relayout="517" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 20" offset="115" eventa="0" eventb="0" eventc="0" operators="0" relayout="518" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 21" offset="121" eventa="0" eventb="0" eventc="0" operators="0" relayout="519" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 22" offset="127" eventa="0" eventb="0" eventc="0" operators="0" relayout="563" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 23" offset="133" eventa="0" eventb="0" eventc="0" operators="0" relayout="564" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 24" offset="139" eventa="0" eventb="0" eventc="0" operators="0" relayout="565" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 25" offset="145" eventa="0" eventb="0" eventc="0" operators="0" relayout="566" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 26" offset="151" eventa="0" eventb="0" eventc="0" operators="0" relayout="567" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 27" offset="157" eventa="0" eventb="0" eventc="0" operators="0" relayout="568" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 28" offset="163" eventa="0" eventb="0" eventc="0" operators="0" relayout="573" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 29" offset="169" eventa="0" eventb="0" eventc="0" operators="0" relayout="578" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 30" offset="175" eventa="0" eventb="0" eventc="0" operators="0" relayout="587" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 31" offset="181" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 32" offset="187" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 33" offset="193" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 34" offset="199" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 35" offset="205" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 36" offset="211" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 37" offset="217" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 38" offset="223" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 39" offset="229" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
  <Logic name="Logic 40" offset="235" eventa="0" eventb="0" eventc="0" operators="0" relayout="0" relaydelay="0" collapsed="false"/>
</Logics>

Et mon but est d'obtenir ce résultat, via VBA :
Nom : XML.PNG
Affichages : 409
Taille : 86,6 Ko

J'ai essayé de cette manière :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
    Dim objMapMLogic As XmlMap
 
 
    Set objMapMLogic = ActiveWorkbook.XmlMaps("MLogic")
 
    ThisWorkbook.XmlImport _
        URL:="D:\M-Logic.xml", _
        ImportMap:=objMapMLogic, _
        Overwrite:=True, _
        Destination:=Worksheets("MLogic_XML").Range("$A$1")

Mais le résultat escompté n'est pas celui obtenu puisque les attributs de "Logics" sont répétés autant de fois qu'il y a de lignes "Logic" et ça rend le xml non exportable...

Je me suis orienté dans ce sens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
    Dim objxmlMap As XmlMap
 
    Dim objlstMLogic As ListObject
    Dim objlstCol As ListColumn
 
    Dim strPath As String
 
    Dim blnMapMLogicExiste As Boolean
 
    For Each objxmlMap In ActiveWorkbook.XmlMaps
        If objxmlMap = "MLogic" Then
            blnMapMLogicExiste = True
            Exit For
        End If
    Next
 
    If blnMapMLogicExiste = False Then
        Set objxmlMap = ActiveWorkbook.XmlMaps.Add("D:\M-Logic.xml" , "Logics")
        objxmlMap.Name = "MLogic"
    End If
 
    Set objlstMLogic = ActiveSheet.ListObjects.Add
 
    strPath = "/Logics/Logic/name"
 
    objlstMLogic.ListColumns(1).XPath.SetValue objxmlMap, strPath

Puis je comptais ajouter les autres colonnes à la suite... Sauf que la dernière ligne me génère l'erreur :
Run-time error '-2147467259 (80004005):

The XPath is not valid because either the XPath syntax is incorrect or not supported by Excel.

Je ne doit pas être trop loin de la solution, je pense que c'est juste ma syntaxe à la ligne :
strPath = "/Logics/Logic/name"

Si quelqu'un pouvait me donner un petit coup de pouce...

Merci d'avoir pris le temps de me lire, et merci de votre aide

Mapper les éléments d'un XML sur une feuille via VBA [XL-2010]

Macros et VBA Excel

Mode arborescent

Discussions similaires

Partager

Partager