VBA Export schéma html

Macro exportant les tableaux d’un fichier avec le nom de chacune des colonnes pour en faire une liste (schéma conceptuel, ou relation universelle).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub exportSchemaHtml()
    Debug.Print "<ul>"
    For Each sh In ActiveWorkbook.Sheets
        For Each tb In sh.ListObjects
            Debug.Print "<li>" & vbCrLf & "<span class=" & Chr(34) & "tname" & Chr(34) & ">" & tb.Name & "</span> ("
            For Each col In tb.ListColumns
                If col.Index = tb.ListColumns.Count Then sep = ")" Else sep = ","
                If col.Range(1) = 1 Then
                    Debug.Print "<span class=" & Chr(34) & "cp" & Chr(34) & ">" & col.Name & "</span>" & sep
                Else
                    Debug.Print "<span class=" & Chr(34) & "at" & Chr(34) & ">" & col.Name & "</span>" & sep
                End If
            Next col
            Debug.Print "</li>"
        Next tb
    Next sh
    Debug.Print "</ul>"
End Sub

Résultat:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<ul>
        <li>
            <span class="tname">livre</span> (
            <span class="cp">id</span>,
            <span class="at">titre</span>,
            <span class="at">isbn</span>,
            <span class="ce">auteur</span>)
        </li>
        <li>
            <span class="tname">auteur</span> (
            <span class="cp">id</span>,
            <span class="at">nom</span>,
            <span class="at">prenom</span>)
        </li>
</ul>

Avec le CSS:

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
#schemaRel {
    border-bottom: 1px solid lightgrey;
    margin-bottom: 10px;
}
 
#schemaRel .tname {
    font-weight:bold;
}
 
#schemaRel .cp {
    text-decoration:underline;
}
#schemaRel .cp:before {
    font-size:smaller;
    content: "<img draggable="false" role="img" class="emoji" alt="🔑" src="https://s.w.org/images/core/emoji/15.0.3/svg/1f511.svg">";
}
 
#schemaRel .ce {
    font-style:italic;
}
#schemaRel .ce:before {
    font-style:normal;
    font-size:smaller;
    content: "️<img draggable="false" role="img" class="emoji" alt="#️⃣" src="https://s.w.org/images/core/emoji/15.0.3/svg/23-20e3.svg">";
}
Partagez: