Rechercher et remplacer dans les fichiers d'un dossier

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Rechercher et remplacer dans les fichiers d'un dossier

Dans cette formation VBA Excel, nous proposons de monter une application fort précieuse permettant de remplacer n'importe quelle occurrence par une autre, dans tous les fichiers contenus dans un dossier. Ce dossier doit être spécifié par l'utilisateur à l'aide d'une boîte de dialogue Windows standard. L'objectif est d'automatiser les actions de mises à jour par traitements récursifs en exploitant les objets VBA permettant de manipuler les fichiers et dossiers.



Dans l'application finalisée illustrée par la capture ci-dessus, deux boutons sont à disposition de l'utilisateur pour enclencher les actions. Des zones sont prévues pour stocker les informations comme le chemin d'accès, le terme recherché et le terme de remplacement.

Sources et présentation du concept
Pour réaliser une simulation intéressante, nous proposons d'agir sur des fichiers de cache Html qui servent de sources à une page Web, dotée d'un moteur de recherche. N'importe lequel d'entre eux peut être appelé pour restituer les informations qu'il contient, en fonction des mots clés tapés par l'internaute. Mais le code qui compose ces fichiers doit subir des mises à jour pour s'adapter à la page Internet de réception. Il serait absurde d'envisager des modifications manuelles, d'autant qu'ils sont tous concernés par les mêmes traitements. Nous exploiterons cette page Web, pour visualiser concrètement les changements opérés par notre application. Pour ce faire, nous avons besoin de récupérer les sources.

• Télécharger le fichier rechercher-remplacer-dossier.rar en cliquant sur son lien,
• Le décompresser dans le dossier de votre choix,

La décompression propose deux fichiers à la racine. Vous notez tout d'abord la présence du classeur rechercher-remplacer-dossier.xlsm, pour l'application VBA à construire. Et vous notez de même la présence du fichier de la page Web saisie-semi-automatique.html. Ce dernier est accompagné de ses ressources externes dans les sous dossiers, nécessaires pour fonctionner, comme nous l'ont appris les formations au développement Web en Javascript.

• Double cliquer sur le fichier saisie-semi-automatique.html pour l'ouvrir dans un navigateur,

Le code Javascript de cette page Html n'est pas fonctionnel. Ce n'est pas ce qui nous intéresse ici. Mais dès l'ouverture, elle charge l'un des fichiers de cache dans son contenu. N'importe quel autre fichier résidant dans le sous dossier cache est susceptible d'être chargé dans la page, en lieu et place. Et plusieurs imperfections sautent aux yeux. Tout d'abord nous constatons la présence d'images brisées. Les liens Html apparaissent en bleu souligné, ce qui fait penser aux très anciens sites Internet. Les rubriques ne sont pas suffisamment espacées. Et enfin, même s'il faut cliquer dessus pour s'en apercevoir, les liens Html sont eux aussi brisés. Ils conduisent vers des pages qui n'existent pas, à cause d'un préfixe manquant et donc à ajouter grâce à l'application VBA Excel.

• Dans le dossier de décompression, double cliquer sur le sous dossier cache pour accéder à son contenu,

Vous y notez la présence de plus de 400 fichiers Html. Ce nombre a volontairement été réduit pour simplifier l'importation. Bien que proposant tous un contenu différent, ils possèdent la même structure avec les mêmes imperfections que nous avons notées sur la page Web.

• Cliquer avec le bouton droit de la souris sur le fichier absolues-excel.html par exemple,
• Dans le menu contextuel qui apparaît, choisir de l'ouvrir avec un éditeur tel que le Notepad,

Comme vous le remarquez, ces fichiers sont constitués d'un code Html linéaire, construit par un code serveur pour permettre des restitutions de bases de connaissances, selon les termes fréquemment tapés par les internautes. Ce fichier en l'occurrence pèse 9ko, il contient donc environ 9000 caractères.

Naviguer dans les dossiers du système d'exploitation
Il est temps de s'intéresser au classeur Excel dont l'objectif à l'issue sera de permettre le nettoyage de tous ces fichiers ou autres, dans un traitement récursif. N'importe quel terme ou expression doit pouvoir être remplacée par une autre. Mais avant cela, nous devons permettre à l'utilisateur de désigner le dossier dans lequel le traitement automatisé doit intervenir.

• A la racine du dossier de décompression, double cliquer sur le fichier rechercher-remplacer-dossier.xlsm, pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Seule la feuille Traitement compose ce classeur. La cellule B6 est prévue pour stocker le chemin d'accès au dossier, défini par l'utilisateur au clic sur le bouton Dossier. Les cellules B9 et B11 sont respectivement prévues pour mémoriser le terme à remplacer et le terme de remplacement. Le bouton Démarrer doit alors lancer le traitement sur tous les fichiers contenus dans le dossier spécifié en B6. Sa mission est de réaliser le remplacement des termes dans chacun d'entre eux, en fonction des indications contenues en B9 et B11.

Nous devons donc proposer à l'utilisateur de spécifier un dossier, à l'aide du premier bouton. Le ruban Développeur est nécessaire pour ces manipulations. S'il n'est pas présent dans votre environnement, la formation pour débuter la programmation en VBA Excel, rappelle comment l'afficher.

• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Puis, sur la feuille Excel, double cliquer sur le bouton 1/ Dossier,

Nous basculons ainsi dans l'éditeur de code Visual Basic Excel, entre les bornes de la procédure chemin_Click. Le code VBA que nous y ajouterons se déclenchera donc au clic sur ce bouton.

Pour piloter les boîtes de dialogue standards de Windows, la référence Microsoft Office 16.0 Object Library a été ajoutée au projet Visual Basic. Le numéro dépend de la version du pack Office. Une différence de version ne procure aucune incidence.



Si cette librairie n'est pas ajoutée, les classes ne peuvent pas être instanciées. En conséquence, les objets permettant de piloter ces boîtes de dialogue, ne peuvent pas être créés.

• Entre les bornes de la procédure, ajouter les déclarations suivantes :

Dim bDialogue As Office.FileDialog
Dim chDossier As String

Très simplement, nous déclarons une première variable comme un objet de boîte de dialogue standard. Nous le nommons bDialogue. Puis nous déclarons une variable de type chaîne de caractères afin de pouvoir réceptionner le chemin d'accès désigné par l'utilisateur, au travers de cette boîte de dialogue.

Après les déclarations, suivent les affectations.

• A la suite du code, ajouter les deux instructions suivantes :

Set bDialogue = Application.FileDialog(msoFileDialogFolderPicker)
bDialogue.Title = "Sélectionner un dossier à parcourir"

Grâce à la propriété FileDialog de l'objet VBA Application, nous instancions la classe permettant de piloter un type de boîte de dialogue. Ce type est défini en paramètre de la propriété. Au cours de la saisie, c'est la liste IntelliSense qui nous guide. En l'occurrence, le paramètre msoFileDialogFolderPicker définit une boîte de dialogue permettant de parcourir et désigner des dossiers. A ce stade, notre objet bDialogue a donc hérité des propriétés et méthodes nécessaires pour les manipuler. Et pour preuve, sur la ligne suivante, nous exploitons la propriété Title afin de personnaliser la barre de titre de la boîte de dialogue. Et c'est donc une méthode qui va permettre de déclencher son affichage.

• A la suite du code VBA, ajouter les instructions suivantes :

If bDialogue.Show = -1 Then
chDossier = bDialogue.SelectedItems(1)
Range("B6").Value = chDossier
End If

La méthode Show de l'objet bDialogue renvoie une valeur numérique. Si elle vaut 1, elle indique que l'utilisateur a bien sélectionné un dossier sans se défausser. C'est la raison pour laquelle nous intégrons le traitement dans une instruction conditionnelle If. La propriété SelectedItems renvoie le chemin d'accès ainsi désigné. Le paramètre 1 est nécessaire pour préciser qu'il s'agit du premier dossier de la sélection bien que nous n'ayons pas paramétré la multi sélection. Nous stockons ce chemin dans la variable chDossier puis l'inscrivons en cellule B6 de la feuille Excel.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + F11),
• Dans le ruban Développeur, cliquer sur le bouton Mode création pour le désactiver,
• Cliquer alors sur le bouton 1/ Dossier de la feuille,
• Dans la boîte de dialogue qui suit, sélectionner le sous dossier cache du dossier de décompression,
• Puis, valider par Ok,

Une fois la validation prononcée, vous constatez que le chemin d'accès complet au dossier, est parfaitement restitué dans la cellule B6, comme nous l'avons mentionné dans le code VBA par affectation. Il s'agit de l'information essentielle pour que le code Visual Basic puisse parcourir l'ensemble des fichiers qu'il contient, afin d'y opérer les remplacements automatisés.

Le code complet de la procédure chemin_Click, est le suivant :

Private Sub chemin_Click()
Dim bDialogue As Office.FileDialog
Dim chDossier As String

Set bDialogue = Application.FileDialog(msoFileDialogFolderPicker)
bDialogue.Title = "Sélectionner un dossier à parcourir"

If bDialogue.Show = -1 Then
chDossier = bDialogue.SelectedItems(1)
Range("B6").Value = chDossier
End If
End Sub

Nettoyer la plage d'extraction
Avant de procéder à l'examen du dossier par traitement récursif, nous devons penser qu'il est nécessaire de supprimer les informations d'un potentiel précédent traitement. Ces informations seront inscrites à partir de la ligne 8, entre les colonnes G et I. Nous avons régulièrement réalisé ce genre de traitement à l'aide d'une boucle While qui permet de poursuivre l'exécution tant qu'un critère est vérifié, en l'occurrence ici, tant qu'une cellule non vide est détectée.

• Dans le ruban Développeur, cliquer de nouveau sur le bouton Mode création,
• Puis, sur la feuille Excel, double cliquer sur le bouton 2/ Démarrer,

Nous générons ainsi la procédure événementielle demarrer_Click, celle qui s'exécutera au clic sur ce bouton. Le code ne peut être déclenché que si trois conditions sont satisfaites. Le dossier doit être spécifié. Les termes de recherche et de remplacement doivent être définis. En d'autres termes, les cellules B6, B9 et B11 ne doivent pas être vides.

• Dans les bornes de la procédure demarrer_Click, ajouter les instructions suivantes :

If (Range("B6").Value = "") Then
MsgBox ("Vous devez désigner un dossier à parcourir avec le premier bouton")
Exit Sub
End If

If (Range("B9").Value = "") Then
MsgBox ("Vous devez spécifier un terme à remplacer")
Exit Sub
End If

If (Range("B11").Value = "") Then
MsgBox ("Vous devez spécifier un terme de remplacement")
Exit Sub
End If

Il s'agit de trois traitements conditionnels équivalents, seule la cellule testée change. Prenons la première en exemple. Si le contenu de la cellule B6 est détecté comme vide : Range("B6").Value = "", alors nous affichons un message à l'utilisateur grâce à la fonction VBA MsgBox, pour corriger le problème. Et dans la foulée, nous exploitons l'instruction Exit Sub pour mettre fin à l'exécution du code.

Si les trois tests ne sont pas concluants, cela signifie que l'utilisateur a renseigné tous les paramètres. Nous devons donc poursuivre l'exécution du programme. Par esprit de structure, nous souhaitons que cette procédure passe la main à deux procédures indépendantes. La première doit se charger de nettoyer la feuille, nous la nommerons nettoyer. La seconde doit de charger de réaliser le traitement récursif pour traiter tous les fichiers du dossier, nous la nommerons traiter_dossier.

• Bien qu'elles n'existent pas encore, réaliser leurs appels, à la suite du code :

Nettoyer
traiter_dossier

Ces deux procédures doivent désormais être créées. Toujours par esprit de structure, nous proposons de les ajouter dans un module.

• Dans l'explorateur de projet, sur la gauche de l'éditeur de code VBA, déployer l'affichage du dossier Modules,
• Puis, cliquer sur l'élément Module1 déjà présent, pour afficher sa feuille de code au centre de l'écran,
• Créer les deux procédures nettoyer et traiter_dossier, comme suit :

Sub nettoyer()

End Sub

Sub traiter_dossier()

End Sub

• Dans les bornes de la procédure nettoyer, ajouter les déclarations et affectation suivantes :

Dim ligne As Integer: Dim colonne As Integer

ligne = 8

Nous avons besoin de parcourir les lignes et certaines colonnes, donc nous déclarons les variables en adéquation. Puis, nous initialisons la ligne sur l'indice 8, soit la position à partir de laquelle le code VBA doit analyser les cellules, pour les vider s'il reste des traces d'extraction. Ensuite, comme nous en avons l'habitude désormais, il s'agit d'enclencher une boucle While qui poursuivra son traitement récursif, tant que son critère est vrai, soit tant que la cellule en cours n'est pas vide.

• A la suite du code, créer la boucle While comme suit :

While (Cells(ligne, 7).Value <> "")

Wend

C'est effectivement la cellule de la ligne 8 en colonne G (indice 7) qui est utilisée comme point de départ de l'analyse. Si la cellule en cours d'étude n'est pas vide, c'est l'ensemble des cases sur les trois colonnes qui doivent être purgées, soit de la colonne G à I. Donc il s'agit d'imbriquer une seconde boucle à l'intérieur de la première, pour simplifier le code et le traitement. Lorsque les bornes sont définies, une boucle For Next est dédiée.

• A l'intérieur de la boucle While, ajouter les instructions suivantes :

For colonne = 7 To 9
Cells(ligne, colonne).Value = ""
Next colonne
ligne = ligne + 1

Pour chaque ligne (ligne = ligne + 1), tant que la cellule de référence n'est pas vide, nous les effaçons toutes (Cells(ligne,colonne).Value = ""), pour l'ensemble des trois colonnes (For colonne = 7 To 9).

Pour tester le code, il s'agit de définir un dossier ainsi que des termes en B9 et B11. Souvenez-nous, si tel n'est pas le cas, les tests VBA que nous avons codés nous bloqueront. De même, il s'agit d'inscrire quelques valeurs temporaires à partir de la ligne 8, sur plusieurs lignes. Enfin, il s'agit de désactiver le mode Création et de cliquer sur le bouton Démarrer.

Le code complet de la procédure nettoyer est le suivant :

Sub nettoyer()
Dim ligne As Integer: Dim colonne As Integer

ligne = 8

While (Cells(ligne, 7).Value <> "")
For colonne = 7 To 9
Cells(ligne, colonne).Value = ""
Next colonne
ligne = ligne + 1
Wend

End Sub

Parcourir tous les fichiers d'un dossier
Comme nous l'avait appris la formation VBA Access pour accéder aux fichiers externes par le code, nous devons instancier la classe qui permettra d'hériter des propriétés et méthodes permettant de manipuler les fichiers et dossiers. Et comme souvent, c'est la fonction VBA CreateObject avec un argument spécifique qui va nous le permettre. Mais avant cela, nous devons déclarer et initialiser les variables nécessaires au traitement.

• Entre les bornes de la procédure traiter_dossier, ajouter les déclarations et affectations suivantes :

Dim nom_dossier As String: Dim fichier As Object
Dim le_dossier, chaque_fichier: Dim flux_lecture
Dim ligne As Integer: Dim le_fichier As String
Dim contenu As String: Dim chercher As String: Dim remplacer As String

nom_dossier = Range("B6").Value
chercher = Range("B9").Value
remplacer = Range("B11").Value
ligne = 8

Nous déclarons entre autres les variables nom_dossier et le_fichier en tant que String afin de pouvoir manipuler les chemins d'accès au dossier et à chacun des fichier parcourus. La variable fichier est déclarée comme un objet pour pouvoir instancier la classe permettant de manipuler les fichiers du disque. Les variables le_dossier et chaque_fichier sont volontairement non typées. Elles le seront à la volée par affectation, afin de manipuler le dossier et chacun des fichiers qui y réside. La variable flux_lecture est prévue pour instancier la classe permettant d'accéder aux fichiers externes, en lecture et en écriture, tout en définissant l'encodage, pour résoudre les problèmes d'accents. Les dernières variables de type String parlent d'elles-mêmes. La variable contenu doit stocker l'information complète du fichier Html en cours de lecture. Les variables chercher et remplacer y seront exploitées pour réaliser les modifications demandées par l'utilisateur.

Comme nous avons déjà affecté les premières variables classiques, juste après les déclarations, nous devons désormais instancier les classes qui permettront aux variables objets, d'hériter des propriétés et méthodes nécessaires.

• A la suite du code, ajouter les affectations suivantes :

Set fichier = CreateObject("scripting.filesystemobject")
Set le_dossier = fichier.getfolder(nom_dossier)
Set flux_lecture = CreateObject("ADODB.Stream")
flux_lecture.Charset = "utf-8"

Grâce au paramètre scripting.filesystemobject passé à la fonction VBA CreateObject, nous instancions la classe permettant à la variable fichier d'hériter des méthodes pour manipuler les fichiers du système. Et précisément, sa méthode getfolder avec en paramètre le chemin du dossier spécifié, permet d'affecter la variable le_dossier. Elle dispose donc désormais des propriétés nécessaires pour piloter ce dossier spécifique. Enfin, nous instancions la classe permettant à l'objet flux_lecture d'accéder au contenu des fichiers externes. Pour cela, nous passons le paramètre ADODB.Stream à la fonction VBA CreateObject. Dans la foulée donc, grâce à sa propriété Charset, nous définissons une bonne fois pour toute l'encodage (utf-8) qui sera utilisé pour tous les fichiers atteints.

Nous disposons désormais de tous les moyens nécessaires pour accéder aux données externes. Comme nous ne connaissons pas à l'avance le nombre de fichiers contenus dans le dossier, nous devons exploiter une boucle de traitement For Each.

• A la suite du code, créer la boucle parcourant les fichiers, comme suit :

For Each chaque_fichier In le_dossier.Files

Next chaque_fichier

Comme nous l'avons expliqué dans de précédentes formations, les objets à gauche et à droite de l'instruction In doivent être du même type. C'est la raison pour laquelle nous n'avions pas typé la variable chaque_fichier. Elle hérite à la volée des propriétés nécessaires pour manipuler chaque fichier qui sera parcouru. C'est la propriété Files de l'objet le_dossier qui renvoie une collection. Cette collection correspond à l'ensemble des fichiers contenus dans le dossier pointé.

• A l'intérieur de la boucle, ajouter les deux affectations suivantes :

le_fichier = nom_dossier & "\" & chaque_fichier.Name
contenu = ""

Grâce à la propriété héritée Name de l'objet chaque_fichier, nous prélevons le nom du fichier en cours de lecture dans la boucle. Nous concaténons cette information au chemin du dossier. Nous obtenons donc le chemin d'accès complet au fichier parcouru. Dès lors, nous pourrons explicitement le désigner pour accéder à son contenu et le prélever. Justement, nous réinitialisons la variable contenu à chaque passage, pour la purger de son ancien traitement.

Rechercher et remplacer dans les fichiers externes
Puisque nous disposons de toutes les informations nécessaires, il est temps d'accéder au contenu de chaque fichier lu, grâce à l'objet flux_lecture. Une fois l'intégralité du fichier réceptionné, nous pourrons le traiter pour y remplacer le terme désigné. Pour que cette modification soit ancrée, nous devrons ensuite enregistrer le fichier sur le disque. Tout traitement se réalise en effet dans la mémoire du système.

• A la suite du code, ajouter les instructions permettant de récupérer le contenu du fichier :

flux_lecture.Open
flux_lecture.LoadFromFile (le_fichier)
contenu = flux_lecture.ReadText()
flux_lecture.Close

La méthode Open de l'objet flux_lecture permet de réserver un accès en mémoire pour y charger le fichier. Et justement, c'est ensuite sa méthode LoadFromFile qui comme son nom l'indique, charge le fichier dont le chemin d'accès complet lui est passé en paramètre. Alors sa méthode ReadText() permet tout simplement de récupérer l'intégralité des données que nous affectons à la variable contenu. Avant de travailler cette dernière, nous n'oublions de libérer la mémoire grâce à la méthode Close de l'objet flux_lecture.

Désormais, la fonction Replace doit opérer les modifications. Suite à cela, nous les sauvegarderons. Comme nous sommes dans une boucle For Each, ces traitements valent pour tous les fichiers parcourus. La syntaxe de la fonction Replace est la suivante :

Replace(Chaîne_de_texte ; Terme_a_remplacer ; Terme_de_remplacement)

• A la suite du code, ajouter les traitements suivants :

contenu = Replace(contenu, chercher, remplacer)

flux_lecture.Open
flux_lecture.WriteText contenu
flux_lecture.SaveToFile le_fichier, 2
flux_lecture.Close

Nous exploitons tout d'abord la fonction Replace sur la variable contenu afin de remplacer toutes les occurrences du terme désigné depuis la feuille, par l'expression de remplacement. Puis, nous exploitons de nouveau l'objet flux_lecture, mais pour enregistrer cette fois. Nous réservons d'abord l'accès en mémoire par sa méthode Open. L'écriture s'y fait par sa méthode WriteText en lui passant la chaîne à écrire (contenu). Puis sa méthode SaveToFile réalise l'enregistrement sous le nom de fichier qui lui est passé en premier paramètre. Une fois la sauvegarde terminée, nous libérons la mémoire par sa méthode Close.

A ce stade, tous les fichiers sont susceptibles d'être modifiés. Mais avant cela, il nous reste à restituer les informations du rapport entre les colonnes G et I, à partir de la ligne 8. Pour ce faire :

• A la suite du code, toujours dans la boucle For Each, ajouter les instructions suivantes :

Cells(ligne, 7).Value = chaque_fichier.Name
Cells(ligne, 9).Value = "Ok"
ligne = ligne + 1

Surtout, nous n'oublions pas d'incrémenter la variable ligne avant de boucler pour que l'inscription du prochain fichier, se réalise bien sur la ligne du dessous.

Exécuter les modifications robotisées sur les fichiers
Pour un codage propre, les objets exploités doivent être vidés afin de libérer les ressources.

• A la suite du code mais après la boucle, ajouter les trois instructions suivantes :

Set flux_lecture = Nothing
Set le_dossier = Nothing
Set fichier = Nothing

C'est comme toujours l'instruction Nothing, par réaffectation, qui permet de détruire ces objets. Pour visualiser les effets des manipulations à suivre, il est intéressant de conserver ouvert le fichier de la page Web. De même, il est intéressant d'ouvrir un fichier de cache, comme absolues-excel.html, dans un éditeur tel que le NotePad.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + F11),

Le premier traitement à réaliser consiste à remplacer dans tous les fichiers, l'information inscrite en L1 par l'information inscrite en L2. En langage Html cette modification ajoute un petit espace entre les calques pour aérer légèrement la présentation. Comme les cellules de réception en B9 et B11 sont des cellules fusionnées, pour recopier les données, il est conseillé de réaliser un copier-coller de barre de formule à barre de formule.

• Répliquer l'information de la cellule L1 dans la cellule B9,
• Répliquer l'information de la cellule L2 dans la cellule B11,
• Puis, cliquer sur le bouton Démarrer,

Le temps d'exécution varie selon la cadence du processeur. Mais étant donné le nombre d'opérations effectuées et le nombre de fichiers traités, la vitesse de traitement est satisfaisante.

• Basculer dans l'éditeur Notepad pour afficher le code de la page Html,

Comme vous le remarquez, l'éditeur indique que des modifications ont eu lieu et propose de mettre à jour l'affichage du fichier. Vous devez donc valider cette proposition. Et comme l'illustre la capture ci-dessus, l'attribut style a parfaitement été ajouté dans les balises div reconnues par leur classe titre_cat. Il est important de comprendre que toutes les balises div de tous les fichiers ont ainsi été affectées. Ces pages Html proposent au-delà de 10 résultats chacune et il y a plus de 400 pages. En conséquence, notre code VBA Excel a réalisé plus de 4000 modifications dans ces fichiers externes. Le principe reste le même si le nombre de fichiers grandit. Nous constatons donc tout l'intérêt de ces actions robotisées qui réalisent en un temps record, ce qu'il aurait été inconcevable de faire manuellement.

• Basculer sur le navigateur de la page Web et la rafraîchir avec la touche F5,

Vous remarquez en effet que les lignes des rubriques s'espacent quelque peu, pour une présentation plus agréable. Nous allons désormais procéder à des remplacements dont l'incidence visuelle est plus marquée.

• Revenir sur la feuille du classeur Excel,
• Répliquer l'information de la cellule L4 dans la cellule B9,
• Répliquer l'information de la cellule L5 dans la cellule B11,
• Puis, cliquer sur le bouton Démarrer,

Tout d'abord nous remplaçons la cible du lien _self par _blank pour que la page de destination soit ouverte dans un nouvel onglet, plutôt qu'en lieu et place. Puis, nous ajoutons l'attribut Style pour que ces liens ne soient plus soulignés (text-decoration:none) et que leur couleur bleue soit remplacée par du gris (color:#666).

Si vous basculez dans l'éditeur Notepad, une nouvelle mise à jour est demandée à cause de modifications externes, celles engagées par notre code VBA Excel. Si vous affichez le contenu de l'une des balises a href, vous notez effectivement que la modification et l'ajout ont parfaitement été opérés. Encore une fois, toutes les balises de cette nature pour l'ensemble des pages Web ont été affectées. Nous proposons de constater les incidences visuelles, plus probantes cette fois.

• Basculer sur la fenêtre du navigateur et rafraîchir la page avec la touche F5 du clavier,

Cette fois, comme l'illustre la capture ci-dessus, les modifications visuelles sont beaucoup plus significatives. Le soulignement pour l'ensemble des liens a effectivement disparu. De même le bleu a bien été remplacé par un gris plus sobre.

Un problème persiste néanmoins. Ces liens sont brisés car leur url n'est pas complète. Si vous cliquez sur l'un d'entre eux, vous êtes redirigé sur une page d'erreur. Cette url est définie par l'attribut href d'une balise a.

<a href='mastermind-creer-un-jeu-avec-excel-200.html'...

Il manque le préfixe du site internet (https://www.bonbache.fr/). La dernière opération de recherche et remplacement, consiste donc à ajouter ce préfixe dans tous les attributs href, pour toutes les pages Html du dossier.

• Revenir sur la feuille du classeur Excel,
• Répliquer l'information de la cellule L8 dans la cellule B9,
• Répliquer l'information de la cellule L9 dans la cellule B11,
• Puis, cliquer sur le bouton Démarrer,

Pas d'incidence visuelle cette fois, mais une correction majeure dans le résultat et la fonctionnalité de la page Web. Si vous rafraîchissez cette dernière, au clic sur l'un des liens, vous êtes désormais parfaitement redirigé sur l'une des rubriques de formation du site Bonbache.fr.

Le code complet de la procédure traiter_dossier est le suivant :

Sub traiter_dossier()
Dim nom_dossier As String: Dim fichier As Object
Dim le_dossier, chaque_fichier: Dim flux_lecture
Dim ligne As Integer: Dim le_fichier As String
Dim contenu As String: Dim chercher As String: Dim remplacer As String

nom_dossier = Range("B6").Value
chercher = Range("B9").Value
remplacer = Range("B11").Value
ligne = 8

Set fichier = CreateObject("scripting.filesystemobject")
Set le_dossier = fichier.getfolder(nom_dossier)
Set flux_lecture = CreateObject("ADODB.Stream")
flux_lecture.Charset = "utf-8"

For Each chaque_fichier In le_dossier.Files
le_fichier = nom_dossier & "\" & chaque_fichier.Name
contenu = ""
flux_lecture.Open
flux_lecture.LoadFromFile (le_fichier)
contenu = flux_lecture.ReadText()
flux_lecture.Close

contenu = Replace(contenu, chercher, remplacer)
flux_lecture.Open
flux_lecture.WriteText contenu
flux_lecture.SaveToFile le_fichier, 2
flux_lecture.Close

Cells(ligne, 7).Value = chaque_fichier.Name
Cells(ligne, 9).Value = "Ok"
ligne = ligne + 1
Next chaque_fichier

Set flux_lecture = Nothing
Set le_dossier = Nothing
Set fichier = Nothing

End Sub

Remplacer une information variable
Il nous reste en effet à corriger le problème d'affichage des images dont le lien est brisé. Nous ne possédons pas ces images. Il ne s'agit donc pas de réaliser un correctif mais de supprimer toutes les balises qui les concernent et ce, dans toutes les pages Html bien évidemment. Mais, il n'y a pas de règle stricte pour les déceler. Leur contenu change d'un lien à un autre et d'une page à une autre. En revanche, nous connaissons leur signature. Nous savons qu'elles débutent comme ceci : <img, et qu'elles se terminent comme cela : /> :

<img class='img_cat' src='MasterMind-creer-un-jeu-avec-Excel 2017-06-02-48-02.jpg' alt='' />

Nous avons déjà appris comment procéder face à cette problématique grâce à la formation précédente sur le traitement des chaînes de caractères par les expressions régulières. Il s'agit de construire un motif indiquant comment débute la chaîne à trouver et comment elle se termine, tout en précisant que les caractères situés entre ces informations sont inconnus et possèdent un nombre indéfini. Pour cela, nous avons encore besoin d'instancier une classe, grâce à la fonction VBA CreateObject, afin de manipuler ces expressions régulières.

Nous pourrions très bien personnaliser ce traitement en prévoyant deux zones supplémentaires sur la feuille Excel. L'utilisateur y indiquerait la séquence de début ainsi que la séquence de fin. Mais ici, nous allons nous contenter de créer une fonction indépendante pour ce traitement particulier.

• Basculer dans l'éditeur de code VBA Excel,
• Après la fonction traiter_dossier, créer la fonction decoupe_spec, comme suit :

Function decoupe_spec(chaine As String) As String
Dim expReg As Object: Dim motif As Object

Set expReg = CreateObject("vbscript.regexp")
expReg.Pattern = "(<img)[0-9a-zA-Z_   \/.:;'=-]*(/>)"
Set motif = expReg.Execute(chaine)

While motif.Count > 0
chaine = expReg.Replace(chaine, "")
Set motif = expReg.Execute(chaine)
Wend

decoupe_spec = chaine
End Function

La vocation d'une fonction est de retourner le résultat du traitement qu'elle réalise, à la procédure qui l'appelle. Ce renvoi se réalise implicitement par l'affectation de ce résultat au nom même de la fonction (decoupe_spec = chaine). Nous lui affectons un paramètre (chaine As String) qui est le contenu du fichier, à chaque passage dans la boucle, à nettoyer de ces balises img. Nous déclarons les deux objets nécessaires pour piloter les expressions régulières : expReg et motif.

Nous instancions la classe vbscript.regexp, grâce à la fonction CreateObject. De fait, l'objet expReg hérite de toutes les propriétés et méthodes permettant de manipuler ces expressions régulières. Ainsi, nous exploitons sa propriété Pattern pour définir le motif à rechercher : (<img)[0-9a-zA-Z_   \/.:;'=-]*(/>). Par les parenthèses, nous indiquons strictement comment débute et se termine chaque occurrence à trouver. Entre les crochets, nous indiquons tous les caractères qu'il est possible de rencontrer comme des lettres, des chiffres ou encore des caractères spéciaux. L'étoile après les crochets spécifie que leur nombre est inconnu et indifférent.

Grâce à sa méthode Execute, nous instancions l'objet motif qui de fait, hérite des propriétés nécessaires pour tester ce pattern sur la chaîne de caractères passée en paramètre. Ainsi, grâce à sa propriété Count, nous enclenchons une boucle qui tourne tant que le motif est trouvé. Et à chaque fois qu'il est trouvé, nous le supprimons grâce à la méthode Replace de l'objet expReg. Avant de boucler, nous redéfinissons la recherche sur la prochaine occurrence (motif = expReg.Execute(chaine)), pour réactualiser le critère.

• Dans la boucle de la procédure traiter_dossier, après la ligne : contenu = Replace(contenu, chercher, remplacer), ajouter l'appel suivant :

contenu = decoupe_spec (contenu)

Ainsi, pour chaque fichier, dès lors que son contenu est récupéré et traité, nous le passons à la fonction decoupe_spec qui se charge de le purger des balises img, et qui retourne le résultat en lieu et place.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Saisir exactement les mêmes termes en B9 et B11, par exemple href=', pour n'enclencher aucun remplacement,
• Puis, cliquer sur le bouton Démarrer,
• Basculer sur le navigateur Web et rafraîchir la page,

Comme vous le constatez, tous les liens brisés ont disparu puisque toutes les balises img ont été supprimées. Les expressions régulières démontrent toute leur puissance lorsqu'il s'agit de remplacer des termes proches ou ressemblants, par le biais d'actions robotisées, ici pilotées en VBA Excel.