Moteur de recherche VBA Excel insensible aux accents

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


Sujets que vous pourriez aussi aimer :


Moteur de recherche Excel par mots clés assemblés

Dans cette formation VBA Excel, nous proposons de créer un véritable Moteur de recherche, sur la base des travaux précédents. Il doit permettre de saisir plusieurs mots clés d'affilée et d'ignorer les différences dues aux caractères latins. L'objectif principal est donc d'offrir des résultats d'extraction affinés, tout en restituant les images attachées aux enregistrements correspondants.



Dans l'exemple ci-dessus de l'application finalisée, l'utilisateur cherche les hôtels référencés dans le département de l'Ardèche. Nous exploitons en effet la base de données des idées de sorties, qui nous a accompagnée dans les précédentes étapes. Dans la zone d'extraction située juste en dessous du moteur de recherche, vous constatez que tous les enregistrements correspondants sont extraits. De même, les images attachées sont importées pour offrir des résultats riches et explicites. L'utilisateur peut taper autant de mots clés de recherche qu'il le souhaite. C'est au code VBA qu'il relève d'isoler tous les enregistrements concordants. Enfin, les caractères latins ne doivent plus poser de souci dans la comparaison des termes. Dans l'exemple ci-dessus, l'utilisateur a tapé le nom du département sans l'accent. Et bien qu'il soit référencé avec accent dans la base de données source, le code VBA les a trouvés et restitués avec leurs images.

Source et présentation de la problématique
Dans les précédentes étapes, nous avons relevé plusieurs défis. Tout d'abord, nous avons réussi à importer les données Multimédias issues d'une base de données externe. Puis, nous avons créé une fonction VBA capable de supprimer les accents, pour ignorer ces caractères latins dans les recherches textuelles. Nous proposons donc de récupérer ces travaux afin de les exploiter.

• Télécharger le classeur moteur-de-recherche.xlsm en cliquant sur son lien,
• Puis, l'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles. La base de données se trouve dans la feuille Importation. Nous avions restitué les enregistrements et leurs images lors de la formation VBA Excel pour importer les données multimédias. Grâce aux propriétés fixées par le code, chacune des photos est liée à la cellule dans laquelle elle est insérée.

La feuille Extraction propose le moteur de recherche à concevoir. Nous l'avions exploitée lors de la formation Excel pour extraire les images d'une recherche par calculs. Le résultat était intéressant mais pas totalement satisfaisant puisqu'il ne permettait pas l'assemblage des mots clés. De même, il était sensible aux caractères latins. Vous notez la présence du bouton Chercher. Sa mission est de lancer l'exécution du code VBA pour réaliser l'extraction selon les termes saisis par l'utilisateur. Il est déjà attaché à une procédure existante qui se nomme Chercher.

• Cliquer avec le bouton droit de la souris sur le bouton Chercher,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier,

Cette action a pour effet de basculer l'affichage dans l'éditeur de code Visual Basic Excel, entre les bornes de la procédure Chercher. Vous notez de même la présence de deux autres procédures de code, nommées respectivement purger et sansAccent.

La procédure purger offre le code VBA permettant de nettoyer la zone d'extraction avant une nouvelle importation. Elle est d'ailleurs appelée dans les bornes de la procédure Chercher du bouton. Si vous tapez du texte sur la feuille extraction à partir de la ligne 7, entre les colonnes B et G et que vous cliquez sur le bouton Chercher, toutes les données disparaissent.

La procédure sansAccent est plus précisément une fonction. Elle retourne donc une valeur après traitement. Cette fonction est celle que nous avons construite lors de la formation VBA Excel pour ignorer les accents dans les comparaisons. En récupérant ces travaux précédents, nous pouvons concentrer nos efforts sur la conception du moteur de recherche par le code VBA. Et vous le constaterez, nous allons enclencher un développement relativement simple par rapport à la puissance livrée en retour.

Découper les mots clés de recherche
Le principe consiste à réceptionner les termes saisis par l'utilisateur et à isoler chaque mot-clé dans un tableau de variables. Chacun pourra alors être cherché dans les différents champs de la base de données. Si tous concordent pour un même enregistrement, l'intégralité de ses données doit être rapatriée sur la feuille Extraction. Nous devons commencer par déclarer les variables nécessaires au traitement.

• Entre les bornes de la procédure Chercher, ajouter les déclarations de variables suivantes, juste avant l'appel de la procédure purger :

Dim tab_mots() As String
Dim compteur As Byte
Dim ligne As Integer: Dim ligne_ext As Integer
Dim valider As Boolean
Dim nom As String: Dim act As String
Dim dep As String: Dim ville As String
Dim chaine As String

Tout d'abord, tab_mots est le tableau de variables dans lequel nous souhaitons ranger tous les mots clés, après les avoir séparés. La variable compteur sera exploitée pour boucler sur l'ensemble de ces mots clés, afin de les comparer tous. Les variables ligne et ligne_ext permettront de nous déplacer en ligne, respectivement pour les feuilles Importation et Extraction. La variable valider est déclarée comme un Booléen. Elle doit servir d'indicateur pour savoir si la recherche est fructueuse. Ensuite, les variables nom, act, dep et ville sont déclarées comme des chaînes de caractères. Si la comparaison est concluante, elles doivent prélever les informations respectives de champs, pour l'enregistrement en cours de lecture, afin de les restituer dans la feuille Extraction, comme résultats de recherche. Enfin, la variable chaine servira à concaténer toutes les informations, pour chaque enregistrement lu, afin de simplifier la recherche de tous les mots clés.

Il convient désormais de réaliser les affectations qui permettront de poursuivre le traitement.

• Après l'appel de la procédure purger, ajouter les lignes de code VBA suivantes :

tab_mots = Split(Range('C4').Value, ' ')
ligne = 3: ligne_ext = 7

Nous avons exploité la fonction Split à maintes reprises, comme dans la formation VBA pour récolter et consolider les données. Elle permet de découper une chaîne de caractères qui lui est passée en premier argument. Cette chaîne est ici le contenu de la zone de recherche (Range('C4').Value), soit les termes tapés par l'utilisateur. Cette découpe se réalise sur le caractère qui lui est passé en second paramètre. Ici, il s'agit de l'espace. Elle retourne autant de mots clés que d'espaces existent pour les séparer. Comme ils sont affectés à la variable tab_mots, ils sont tous rangés dans le tableau sur une rangée différente. Nous pourrons donc y accéder indépendamment. Enfin, nous initialisons les variables de ligne sur les points de départ. Les informations de la base de données sont énumérées à partir de la ligne 3. La zone d'extraction quant à elle commence à la ligne 7.

Il s'agit maintenant de parcourir tous les enregistrements de la feuille Importation à la recherche de correspondances. La boucle While est dédiée. Avec un critère sur la cellule, elle permet de poursuivre son traitement sur toutes les lignes, tant qu'elles sont détectées comme non vides. Dans ce traitement récursif, nous devons prélever les informations de champ pour chaque enregistrement et les concaténer. Cette chaîne servira de recherche pour les mots clés tapés par l'utilisateur.

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

While Sheets('Importation').Cells(ligne, 3).Value <> ''
valider = True

nom = Sheets('Importation').Cells(ligne, 3).Value
act = Sheets('Importation').Cells(ligne, 4).Value
dep = Sheets('Importation').Cells(ligne, 5).Value
ville = Sheets('Importation').Cells(ligne, 6).Value

chaine = nom & '-' & act & '-' & dep & '-' & ville

ligne = ligne + 1
Wend

Le test de la boucle est effectué sur la troisième colonne, soit celle du nom. Nous aurions aussi pu le faire sur l'identifiant, soit la deuxième colonne de la feuillle Importation. Comme nous incrémentons la variable ligne avant de boucler (ligne = ligne + 1), le test est réalisé sur chaque ligne. Le traitement se poursuit tant que la cellule contient une information (Cells(ligne,3).Value <> ''). Nous étudions ainsi tous les enregistrements de la base de données.

A chaque nouvelle boucle, nous réinitialisons la variable booléenne à True. Nous considérons par défaut que la recherche est un succès. Mais avec les traitements qui suivront, il suffira que l'un des mots clés ne soit pas trouvé, pour qu'elle bascule à False, indiquant que la recherche est infructueuse. Ensuite, grâce à la propriété Value de l'objet Cells, nous prélevons les informations de chacun des champs pour l'enregistrement en cours. Nous les affectons aux noms de variables respectifs. Nous concaténons ces dernières séparées d'un tiret dans la variable chaine.

Nous pouvons désormais exploiter cette variable chaine pour la comparaison avec les mots clés. Pour cela, nous devons passer en revue chacun des termes et rechercher sa présence dans la variable concaténée. Il suffit de réaliser une boucle bornée entre la première et la dernière rangée du tableau de variables. La boucle VBA For est donc dédiée. Si l'un des termes n'est pas trouvé, la variable booléenne doit être basculée à False. C'est la fonction VBA InStr qui permet de savoir si une occurrence est présente dans une chaîne, selon la syntaxe suivante :

InStr(pos_depart, chaine_de_recherche, mot_cle_cherché, mode_de_comparaison)

• A la suite du code de la boucle While, avant l'incrémentation de la variable ligne, ajouter la boucle For suivante :

For compteur = 0 To UBound(tab_mots())
If (Len(tab_mots(compteur)) > 3) Then
If (InStr(1, sansAccent(chaine), sansAccent(tab_mots(compteur)), vbTextCompare)= 0) Then
valider = False
Exit For
End If
End If
Next compteur

La fonction VBA UBound permet de connaître la borne supérieure du tableau de variables. Nous le parcourons donc de la première (0) à la dernière ligne (UBound), afin de récupérer chaque mot clé à comparer. Nous ignorons les petits mots de liaison (Le, de, la, les, des etc...). C'est pourquoi une instruction conditionnelle If teste la longueur du mot clé en cours de lecture. Cette longueur de chaîne est retournée grâce à la fonction VBA Len. Si le nombre de caractères est bien supérieur à 3, nous pouvons débuter la comparaison.

Nous débutons donc un nouveau test grâce à la fonction VBA InStr pour chercher la présence de ce mot clé (tab_mots(compteur)), dans la variable chaine, à partir de la première position (1). Nous spécifions le mode de comparaison textuel en quatrième argument (vbTextCompare). Pour que la comparaison ne soit pas sensible aux accents, nous les supprimons des deux textes grâce à la fonction sansAccent (sansAccent(chaine) et sansAccent(tab_mots(compteur))). La fonction InStr renvoie la position de l'occurrence trouvée. Si elle retourne la valeur zéro, cela signifie que le mot clé n'est pas présent dans la chaine concaténée. Donc l'enregistrement en cours de lecture dans la base de données ne concorde pas avec les termes de recherche. En conséquence, nous basculons l'indicateur booléen à False. Puis nous stoppons l'énumération des mots clés pour cet enregistrement grâce à l'instruction Exit For. De fait, la boucle While passe au prochain enregistrement de la base de données. Et l'énumération redémarre pour y trouver les correspondances. Ce processus se poursuit jusqu'à la dernière ligne afin de restituer l'intégralité des informations correspondant à la recherche.

Lorsque tous les mots clés ont été testés, une fois la boucle For terminée, si la variable valider vaut toujours True, cela signifie que chaque mot clé a été repéré dans l'enregistrement en cours. Dans ces conditions, ses données doivent être récupérées et inscrites dans la feuille Extraction, pour la ligne en cours (ligne_ext).

• Après la boucle For mais toujours avant l'incrémentation de la variable ligne, ajouter les instructions VBA suivantes :

If (valider = True) Then
Cells(ligne_ext, 2).Value = nom
Cells(ligne_ext, 3).Value = act
Cells(ligne_ext, 4).Value = dep
Cells(ligne_ext, 5).Value = ville
Cells(ligne_ext, 6).Value = Sheets('Importation').Cells(ligne,7).Value

ligne_ext = ligne_ext + 1

End If

Dans le cas d'un enregistrement concluant, nous affectons les cellules dédiées de la feuille Extraction des informations de champs correspondantes. Seule la description n'avait pas été mémorisée dans une variable. Dans le cas concluant, et seulement dans ce cas, nous incrémentons la variable ligne_ext. En effet, si une prochaine concordance est trouvée, les données doivent être ajoutées à partir de la ligne du dessous (ligne_ext + 1).

Seules les images nous manquent à ce stade. Nous les traiterons après avoir réalisé quelques essais. L'objectif consiste à vérifier que nous pouvons faire une recherche sur plusieurs mots clés, avec ou sans accents.

• Enregistrer les modifications (CTRL + S),
• Basculer sur le classeur Excel (ALT + F11) et activer la feuille Extraction,
• En cellule C4 de la zone de recherche, taper les termes suivants : Hotel Isere, sans accents,
• Puis, cliquer sur le bouton Chercher,

La recherche fonctionne. Nous avons tapé deux mots clés. Ils ont tous deux été trouvés dans un grand nombre d'enregistrements, restitués jusqu'à la ligne 42 de la feuille Extraction. D'ailleurs, nous pourrions prévoir un calcul de synthèse permettant d'afficher ce nombre de résultats. Les termes Isère et Hôtel sont référencés avec accent dans la base de données. Et bien que nous n'ayons pas respecté ces caractères latins dans la saisie, les comparaisons ont été concluantes grâce à la fonction VBA sansAccent. D'ailleurs si vous tapiez les mêmes termes avec accents, soit : Hôtel Isère, vous obtiendriez exactement les mêmes résultats. Nous sommes en train de bâtir un moteur de recherche à la fois souple et puissant. Pour preuve, nous ne sommes pas limités dans l'énumération des mots clés, afin d'affiner les résultats de recherche.

• En cellule C4, taper cette fois la recherche suivante : Parc animalier Ardèche,
• Puis, cliquer sur le bouton Chercher,

Seule une ligne est extraite, preuve que la réponse est pertinente. Seul un enregistrement regroupe les trois mots-clés à la fois.



Il nous reste à proposer les miniatures d'images pour chaque résultat, à intégrer dans la colonne G. Le principe est simple. Il consiste à copier l'image (Copy) pour l'enregistrement en cours, dans la feuille Importation. Puis, il s'agit de sélectionner la cellule de destination (Select) dans la colonne G de la feuille Extraction. Enfin, il suffit de la coller (Paste) pour qu'elle soit insérée et attachée à la cellule désignée. Une macro Excel automatique simulant ces actions nous aurait fourni le code.

• Basculer dans l'éditeur de code VBA Excel (ALT + F11),
• Dans les bornes de la dernière instruction If, avant l'incrémentation de la variable ligne_ext, ajouter les lignes de code VBA suivantes :

Sheets('Importation').Cells(ligne, 8).Copy
Sheets('Extraction').Cells(ligne_ext, 7).Select
ActiveSheet.Paste

Comme nous avions attaché chaque image à sa cellule en fixant ses propriétés par le code VBA lors de l'importation, nous récupérons bien la photo en copiant simplement la cellule (Cells(ligne,8).Copy). Après avoir sélectionné la destination (Cells(ligne_ext, 7).Select), c'est la méthode Paste de l'objet VBA ActiveSheet qui restitue l'élément multimédia chargé dans le presse papiers.

• Enregistrer les modifications et basculer sur la feuille Extraction,
• Taper les termes de recherche suivants : Restaurant Drôme Valence,
• Puis, cliquer sur le bouton Chercher,

Comme vous le constatez, deux enregistrements sont extraits. Il s'agit bien de restaurants situés à Valence dans la Drôme. Et les photos permettant d'agrémenter les résultats sont elles aussi importées. Le fonctionnement du moteur de recherche est donc tout à fait convaincant. Notez que nous aurions pu nous contenter de taper la recherche suivante : Restaurant Valence. Pour le plaisir, nous avons cumulé les mots clés pour tester la puissance des comparaisons enclenchées par le code Visual Basic Excel. Une fois encore, le terme Drôme a été saisi avec l'accent circonflexe bien qu'il soit référencé sans accent dans la base de données. Et notre moteur de recherche s'est adapté.



Notre moteur de recherche Excel pourrait être amélioré en proposant des résultats proches, lorsque les recherches sont infructueuses par exemple. Mais la base est posée et elle est saine. Libre à vous donc de l'adapter à vos besoins et de poursuivre le développement si vous le souhaitez.

Le code complet de notre moteur de recherche, relativement simple par rapport à sa puissance, est le suivant :

Sub Chercher()
Dim tab_mots() As String
Dim compteur As Byte
Dim ligne As Integer: Dim ligne_ext As Integer
Dim valider As Boolean
Dim nom As String: Dim act As String
Dim dep As String: Dim ville As String
Dim chaine As String

purger

tab_mots = Split(Range('C4').Value, ' ')
ligne = 3: ligne_ext = 7

While Sheets('Importation').Cells(ligne, 3).Value <> ''
valider = True

nom = Sheets('Importation').Cells(ligne, 3).Value
act = Sheets('Importation').Cells(ligne, 4).Value
dep = Sheets('Importation').Cells(ligne, 5).Value
ville = Sheets('Importation').Cells(ligne, 6).Value

chaine = nom & '-' & act & '-' & dep & '-' & ville

For compteur = 0 To UBound(tab_mots())
If (Len(tab_mots(compteur)) > 3) Then
If (InStr(1, sansAccent(chaine), sansAccent(tab_mots(compteur)), vbTextCompare)= 0) Then
valider = False
Exit For
End If
End If
Next compteur

If (valider = True) Then
Cells(ligne_ext, 2).Value = nom
Cells(ligne_ext, 3).Value = act
Cells(ligne_ext, 4).Value = dep
Cells(ligne_ext, 5).Value = ville
Cells(ligne_ext, 6).Value = Sheets('Importation').Cells(ligne,7).Value

Sheets('Importation').Cells(ligne, 8).Copy
Sheets('Extraction').Cells(ligne_ext, 7).Select
ActiveSheet.Paste

ligne_ext = ligne_ext + 1

End If
ligne = ligne + 1
Wend
End Sub