Créer un fichier sitemap.xml utilisé par les moteurs de recherche Utilisation de VBA sous Excel

Octobre 2023

Quelques remarques.

La description ci-dessous de toute la procédure est relativement longue mais il ne faut que quelques secondes pour créer un fichier de 1000 url avec l'outil. Je vous livre ici le comment j'ai fait afin que vous puissiez modifier la procédure pour la personnaliser à votre besoin. Mais si vous le voulez, vous chargez le fichier Excel et vous lancez le module et c'est parti...

Il est possible de créer plusieurs fichiers pour les pages, les images et les vidéos, ici je ne vais m'intéresser qu'à la création d'un fichier de pages html (le plus important à mes yeux).
Il est également possible de créer plusieurs fichiers sitemap pour les pages et les assembler ensemble avec un fichier qui les regroupe, je ne traite pas non plus ce cas ici.
Il existe des sites qui vous permettent de créer en ligne un fichier sitemap en scrutant l'arborescence de votre site malheureusement dans leur version gratuite ils sont limités sur le nombre de page qu'ils enregistrent dans le fichier.
Le module VBA décrit fonctionne sous Excel sur un PC sous Windows, pour l'utiliser sous d'autre système il faut modifier le filtre du type de fichier utilisé pour les fichiers de type html ou htm.

Le fichier sitemap est difficile à créer lorsque le nombre de page augmente !

Mais c'est quoi un fichier sitemap.xml

Cahier des charges du module VBA sous Excel.

Le fichier xml qu'il faut créer doit contenir toutes les pages de votre site.
Pour chaque page le fichier va contenir l'url de la page, la dernière date de modification de la page et un indicateur d'importance de la page.

Pour les url des pages le module VBA va lire le répertoire de votre disque, pour chaque ligne il va modifier le chemin disque (c:\emplacement_site_sur_disque\nom_de_page_xxx.html) en un nom de page (https://nom_de_domaine/nom_de_page_xxx.html).
Puis pour les pages plus loin dans l'arborescence disque (c:\emplacement_site_sur_disque\sous_rep_1\sous_rep_2\sous_rep_3\sous_rep_4\page_yyy.html)
aprés avoir remplacé en (https://nom_de_domaine/sous_rep_1/sous_rep_2/sous_rep_3/sous_rep_4/page_yyy.html)
Le remplacement des \ en / va être effectué afin d'avoir une url propre.

Pour la dernière date de modification du fichier html la date trouvée est simplement adaptée (coté feuille excel) au format sitemap aaaa-mm-jj l'heure étant facultative je ne l'ai pas traitée !

Pour l'indicateur d'importance de la page, la stratégie que j'ai choisie est la suivante :
Plus le répertoire est proche de la racine plus il est important !
Si la page est mémorisée dans un sous-rep de sous-rep elle est moins importante.
Cela ne correspond peut-être pas à votre approche, vous devrez alors modifier l'algorithme pour coller à votre approche ! Le sitemap attend un nombre compris entre 1.00 (le plus important) à 0,10 (le moins important) alors je compte le nombre de / (moins les 2 du https://)
et j'utilise la formule suivante : ( 100 - ( nb * 10 )) / 100

Une autre information peut être mémorisée dans le fichier sitemap, elle est facultative et je ne l'ai pas traitée, c'est un indicateur de fréquence de modification de la page.
Si vous souhaitez utiliser cette info vous pouvez par exemple ajouter dans le nom de votre page html un indicateur pour repérer les fréquences (exemple page_yfy5.html) le module VBA n'aura plus qu'à repérer l'indicateur...

Pour créer ce module je suis parti d'une structure de recherche récursive trouvée sur l'excellent site
developpez.com dans la rubrique Office/Excel/FAQ
J'ai adapté ce module afin de créer l'arborescence décrite ci-dessus.

Deux boites de dialogue vont s'ouvrir successivement afin que vous saisissiez le répertoire de base de l'arborescence de votre site sur votre disque. Puis une deuxième pour saisir le nom de domaine de votre site. Comme expliqué plus haut ceci va permettre de transformer des emplacements disque en url.

Le code est disponible ci-dessous, mais il n'est pas utile de le recopier, je vous mets à dispo un fichier excel qui contient déjà ce code.

Option Explicit
Sub TestListeFichiers()
    Dim mon_Dossier As String
    Dim init_web As String
    'Définit le répertoire sur votre disque local pour débuter la recherche des fichiers html.
    ' exemple Dossier = "C:\wamp\www\castoo"
    mon_Dossier = InputBox("Saisissez le dossier racine du site web : ", "Dossier sur ordinateur", "C:\wamp\www")
    'Définit le début de l'url cmmun a toutes les adresses du site.
    ' exemple init_web = "https://castoo.fr"
    init_web = InputBox("Saisissez le nom du site web : ", "Nom de debut de l'url", "https://castoo.fr")
    'Appelle la procédure de recherche des fichiers
    ListeFichiers mon_Dossier, init_web, mon_Dossier
    'Ajuste la largeur des colonnes A:C en fonction du contenu des cellules.
    Columns("A:C").AutoFit
    MsgBox "Terminé"
End Sub
 
Sub ListeFichiers(mon_Repertoire As String, site As String, mem_rep As String)
    'Nécessite d'activer la référence "Microsoft Scripting RunTime"
        'Dans l'éditeur de macros (Alt+F11):
        'Menu Outils
        'Références
        'Cochez la ligne "Microsoft Scripting RunTime".
        'Cliquez sur le bouton OK pour valider.
    Dim mon_url As String
    Dim Fso As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim i As Long
    Set Fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = Fso.GetFolder(mon_Repertoire)
    'Récupère le numéro de la dernière ligne vide dans la colonne A.
    i = Range("A65536").End(xlUp).Row + 1
    'Boucle sur tous les fichiers du répertoire chaque fichier correspond à une url
    For Each FileItem In SourceFolder.Files
        ' On filtre tous les fichiers avec extension html ou htm
        If FileItem.Type = "Microsoft Edge HTML Document" Then
                'URL remplace le nom du rep par celui du site et les \ par des /
                mon_url = FileItem.ParentFolder & "/" & FileItem.Name
                mon_url = Replace(mon_url, mem_rep, "")
                mon_url = site + mon_url
                mon_url = Replace(mon_url, "\", "/")
                Cells(i, 1) = mon_url
                'Date de dernière modification du fichier html
                Cells(i, 2) = Format(FileItem.DateLastModified, "yyyy/mm/dd")
                'Indique le nombre de / pour determiner un ordre de priorité
                Cells(i, 3) = (100 - (10 * ((Len(Cells(i, 1)) - Len(Replace(Cells(i, 1), "/", ""))) - 2))) / 100
                i = i + 1
        End If
    Next FileItem
    '--- Appel récursif pour lister les fichier dans les sous-répertoires ---.
    For Each SubFolder In SourceFolder.subfolders
        ListeFichiers SubFolder.Path, site, mem_rep
    Next SubFolder
End Sub
	

1er étape : Lancement du module VBA sous Excel.

J'utilise Office 2010 il y a bien plus récent mais je n'ai pas !
Comme vous pouvez le voir sur cette vidéo, lorsque vous chargez le fichier excel trois actions sont à réaliser :
- Valider le petit bandeau jaune qui peut s'afficher en haut de la feuille pour activer les macros.
- Ajouter sur le bandeau l'onglet "Développement" si vous ne l'avez pas déjà, ceci vous permettra de lancer les macros et d'avoir accès à la gestion d'XML.
- Avant de lancer la macro vous devez aussi activer l'option "Microsoft Scripting RunTime".

A la fin de l'exécution du module vous devez obtenir une feuille Excel qui doit ressembler à ceci.
Vous devez vérifier que les url sont bien libellé avec une adresse correcte, vous devez pouvoir prendre une ligne, la mettre dans la barre d'adresse de votre navigateur et la page doit s'afficher.
Vous pouvez également vérifier si la hiérarchie des priorités vous convient.

2eme étape : Création de la structure XML.

La création du fichier xml passe par le chargement (il est possible qu'il soit déjà chargé dans le fichier excel que je fournis plus bas.) d'un fichier de structure xml, celui-ci n'est rien d'autre qu'un mini xml qui contient une ou deux descriptions de la structure xml, les données n'ont aucune importance, seule la structure est importante.
A la création du fichier xml, vous allez constater qu'Excel ajoute un descripteur "ns1" devant chaque nom de la structure xml, pas d'inquiétude nous le supprimerons plus tard...

3eme étape : Nettoyage du fichier sitemap.xml nouvellement créer

4eme étape : Installer votre fichier sitemap dans le moteur de recherche