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).


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:

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

#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: "🔑";
}

#schemaRel .ce {
	font-style:italic;
}
#schemaRel .ce:before {
	font-style:normal;
	font-size:smaller;
	content: "️#️⃣";
}
Partagez: