Saisie semi-automatique pour moteur de recherche Excel

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


Sujets que vous pourriez aussi aimer :


Saisie semi-automatique en VBA Excel

Nous poursuivons ici le développement de l'application précédente qui avait permis de consolider les données issues de fichiers externes. Désormais, nous proposons d'exploiter les chaînes de mots clés de recherches ainsi reconstitués pour réaliser des suggestions pertinentes, au fur et à mesure de la frappe de l'utilisateur, dans le moteur de recherche. En d'autres termes, nous allons bâtir une saisie semi-automatique capable d'offrir des résultats en cohérence avec les premiers caractères tapés.



Le résultat que nous souhaitons atteindre est illustré par la capture ci-dessus de l'application finalisée. Comme vous le remarquez, une liste de propositions se construit en dessous de la zone de frappe. Seuls sont proposés les termes commençant exactement par les mêmes lettres.

Sources et problématique
Il s'agit ici de poursuivre les travaux du développement précédent. Les fichiers de cache seront de nouveaux nécessaires dans le troisième volet qui produira l'extraction correspondant à la recherche. Dans cette application, nous avons surtout besoin de l'un des deux fichiers externes, dans lesquels sont concaténés tous les termes de recherche. Nous pourrons ainsi les manipuler par le code Visual Basic Excel.

• Télécharger le fichier saisie-intuitive-vba.rar en cliquant sur son lien,
• Le décompresser dans le dossier de votre choix,
• A la racine du dossier de décompression, double cliquer sur le fichier saisie-intuitive-vba.xlsm pour l'ouvrir dans Excel,
• Cliquer alors sur le bouton Activer la modification du bandeau de sécurité,

Nous récupérons le classeur constitué des deux feuilles Consolidation et Recherche. La feuille Consolidation est active par défaut. Elle présente la dernière extraction réalisée sur les fichiers de cache par le code VBA. Comme nous n'avons pas importé le sous dossier cache pour cette suite, un clic sur le bouton Récolter génère une erreur. Le sous dossier cache étant introuvable, le code plante.

Mais ce n'est pas important à ce stade. Nous avons bien récupéré les fichiers de consolidation fournis par ce code, développé lors de la formation précédente. Ils vont nous permettre de concentrer nos efforts sur le développement de la saisie intuitive. L'objectif est de proposer des termes de recherche correspondant à la demande de l'utilisateur, au fur et à mesure de la frappe.

• Cliquer sur l'onglet Recherche en bas de la fenêtre Excel pour activer sa feuille,

Vous notez la présence de contrôles ActiveX à piloter par le code VBA. Il y a une zone de texte pour le moteur de recherche. Un bouton est proposé pour valider les termes et lancer l'extraction. Une zone de liste doit réaliser des suggestions en adéquation avec la saisie. Cette dernière ne doit d'ailleurs apparaître que durant la saisie et s'effacer à validation, comme le fait Google lui-même.

Le ruban Développeur est nécessaire pour manipuler ces contrôles. La formation pour débuter la programmation en VBA Excel, rappelle notamment 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, cliquer sur le bouton Mode création,
• Sélectionner alors le contrôle Zone de liste de la feuille, situé sous le moteur de recherche,
• Puis, cliquer sur le bouton Propriétés situé dans la section Contrôles du ruban Développeur,
• Double cliquer sur sa propriété Visible pour basculer sa valeur à False,

Nous définissons ainsi la propriété du contrôle Zone de liste nommé prop, de manière à ce qu'il soit masqué par défaut. Nous le ferons réapparaître automatiquement par le code, au cours de la frappe de l'utilisateur.

Analyser la frappe utilisateur durant la saisie
Un code VBA Excel doit se déclencher à chaque fois que la frappe d'un nouveau caractère est détectée, dans le contrôle zone de saisie nommé moteur. Sa mission est de récupérer la chaîne concaténée des termes de recherche dans le fichier externe, de l'analyser pour en extraire les suggestions qui semblent correspondre à la demande. Ce code doit donc être attaché à une procédure événementielle particulière.

• Sur la feuille Excel, double cliquer sur la zone de saisie nommée moteur,

Nous basculons ainsi dans l'éditeur de code Visual Basic Excel, entre les bornes de la procédure moteur_Change. Comme son nom l'indique, à chaque changement détecté, soit à chaque nouvelle frappe, le code compris entre ses bornes est exécuté.

Comme toujours, nous devons commencer par déclarer les variables nécessaires à la suite du traitement.

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

Dim chaine As String: Dim plancher As Integer: Dim i As Integer
Dim nbMots As Integer: Dim chaine_tab() As String
Dim nbCar As Integer: Dim saisie As String: Dim ligne As Integer

Nous déclarons plusieurs variables comme des chaînes de caractères (String). La variable chaine doit permettre de stocker l'ensemble des mots clés concaténés, issus du fichier externe. Le tableau de variables chaine_tab doit réussir à stocker chaque terme de recherche indépendamment, une fois la découpe réalisée. La variable saisie porte bien son nom, elle doit enregistrer les termes tapés par l'utilisateur.

Nous déclarons aussi des variables comme des entiers (Integer). La variable entier fera office de sécurité pour ne pas lancer l'exécution lorsque la saisie n'est pas avérée. La variable i doit être utilisée comme compteur de boucle pour parcourir les éléments rangés dans le tableau, soit les termes de recherche. La variable nbMots doit permettre de stocker le nombre de termes de recherche rangés dans le tableau. C'est lui qui déterminera ainsi la borne supérieure de la boucle de traitement. La variable nbCar, comme son nom l'indique servira à stocker le nombre de lettres tapées par l'utilisateur. En effet, un des objectifs consiste à ne pas suggérer à l'utilisateur un terme plus court que ce qu'il n'a déjà saisi. Enfin la variable ligne doit être utilisée pour initialiser l'extraction des résultats sur la feuille Excel.

Nous proposons désormais d'accéder au fichier externe en lecture. Celui-ci se nomme mem_cherche.txt. Il est situé dans le sous dossier consolidation du dossier de décompression. Nous allons répliquer les techniques que nous avions apprises grâce à la formation VBA Excel pour importer et exporter les données par le code.

• A la suite du code, ajouter lesinstructions suivantes :

plancher = 0

Open ThisWorkbook.Path & "\consolidation\mem_cherche.txt" For Input As #1
Line Input #1, chaine
Close #1

prop.Clear
MsgBox chaine

Nous initialisons la variable plancher à zéro pour vérifier que les caractères tapés existent bien. L'instruction Open suivi du chemin d'accès complet au fichier ensuite désigné, permet d'ouvrir ce dernier en mémoire. C'est encore une fois la propriété Path de l'objet ThisWorkbook qui retourne le chemin complet jusqu'au classeur actif. Nous complétons ce chemin par concaténation jusqu'à rejoindre le fichier texte. Comme nous souhaitons récupérer son contenu, l'instruction For Input définit un accès en lecture. Ces accès séquentiels se réalisent en mémoire. C'est pourquoi nous lui attribuons un adressage libre (#1) afin de pouvoir ensuite le désigner par le code.

C'est alors l'instructionLine Input qui permet de charger la ligne entière du fichier dans la variable chaine. Pour accéder à la ligne du bon fichier, notez que nous désignons son adresse mémoire, telle qu'elle a été créée (#1). La récupération étant terminée, nous fermons l'accès au fichier grâce à l'instruction Close, toujours en désignant le fichier dans la mémoire par son adresse. Nous libérons ainsi les ressources.

Puis, comme nous nous apprêtons à charger la zone de liste de nouvelles propositions, nous commençons par la vider, grâce à sa méthode Clear. Enfin, nous réalisons un test temporaire d'affichage par la fonction MsgBox. L'objectif est de confirmer que nous avons été en mesure de récupérer et stocker l'ensemble des termes de recherche concaténés.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + F11),
• Cliquer sur la croix rouge de la fenêtre Propriétés pour la masquer,
• Dans le ruban Développeur, cliquer sur le bouton Mode création pour le désactiver,
• Taper alors un premier caractère dans la zone de saisie,

Instantanément, la boîte de dialogue de la fonction MsgBox s'affiche. Nous prouvons donc que nous sommes en mesure de gérer l'événement de la saisie utilisateur mais aussi de récupérer l'intégralité du fichier texte.

Pour un traitement plus productif et une exécution plus réactive, il aurait été judicieux de déclarer la variable chaine en variable publique. Ainsi, une fois le contenu du fichier texte récupéré, le code passe directement au traitement des données. En conséquence, il est possible d'adapter le code comme suit :

Option Explicit

Dim chaine As String

Private Sub moteur_Change()
Dim plancher As Integer: Dim i As Integer
Dim nbMots As Integer: Dim chaine_tab() As String
Dim nbCar As Integer: Dim saisie As String:
Dim ligne As Integer

plancher = 0

If (chaine = "") Then
Open ThisWorkbook.Path & "\consolidation\mem_cherche.txt" For Input As #1
Line Input #1, chaine
Close #1
End If

prop.Clear
MsgBox chaine
End Sub

Mais ici, l'objectif est bien de produire la saisie semi-automatique. A la vitesse du processeur, sur un poste de travail qui n'est pas un serveur, ces adaptations peuvent donc attendre.

• Revenir dans l'éditeur de code (ALT + F11),
• Préfixer l'instruction du MsgBox avec une apostrophe pour la passer en commentaire,

Avant d'envisager le traitement qui consiste à passer tous les termes de recherche en revue afin de restituer ceux qui correspondent, nous devons commencer par purger la zone d'extraction des potentiels précédents résultats. Cette zone d'extraction débute en ligne 6 pour la colonne B. A partir de ce point, les cellules sont fusionnées de la colonne B à la colonne H.

• Pour ce faire, après le MsgBox en commentaire, ajouter la boucle de traitement suivante :

ligne = 6 While

Cells(ligne, 2).Value <> ""
Cells(ligne, 2).Value = ""
ligne = ligne + 1
Wend

Fort logiquement, nous commençons par initialiser l'indice de ligne sur le chiffre 6. Puis nous débutons un traitement récursif qui doit se poursuivre tant que la cellule est détectée comme non vide (While Cells(ligne, 2).Value <> ""). Si tel est le cas, nous vidons le contenu de la ligne en cours grâce à la propriété Value de l'objet Cells. Nous n'oublions pas d'incrémenter la variable ligne à chaque passage afin de réaliser le test sur la ligne suivante.

Pour tester le bon fonctionnement de cette boucle, il suffit de saisir quelques données à partir de la ligne 6 puis de taper quelques caractères dans le moteur de recherche, afin de constater que les cellules sont purgées.

Propositions intuitives lors de la saisie
En guise de suggestion intuitive, il s'agit en fait de restituer des chaînes de caractères plus longues que l'originale mais qui commencent exactement de la même façon. Pour cela, nous devons commencer par ranger chacun des termes indépendamment dans le tableau de variables. Ensuite nous pourrons le parcourir à la recherche des correspondances. A l'instar de la méthode Split en Javascript, la fonction VBA Split permet de découper une chaîne de caractères sur un élément remarquable.

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

chaine_tab = Split(chaine, "|")
nbMots = UBound(chaine_tab())

Le contenu récupéré depuis le fichier texte (chaine) est découpé sur le caractère remarquable de la barre verticale (|). Nous l'avions en effet utilisée pour séparer explicitement chacun des termes de recherche. Nous obtenons un tableau (chaine_tab) dont la dimension s'est adaptée au nombre d'éléments restitués. C'est pourquoi nous exploitons la fonction UBound pour connaître le nombre de lignes sur lequel nous pourrons le parcourir (nbMots).

Mais avant cela, nous devons connaître les lettres tapées par l'utilisateur ainsi que leur nombre de caractères.

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

saisie = moteur.Value
nbCar = Len(saisie)
MsgBox saisie & "-" & nbCar

La propriété Value du contrôle TextBox permet de récupérer le contenu du moteur de recherche au moment de la frappe. Nous l'affectons à la variable saisie. La fonction VBA Len retourne le nombre de caractères contenus dans une chaîne qui lui est passée en paramètre. Nous affectons cette donnée à la variable nbCar. Puis, à titre de vérification, nous affichons ces variables concaténées à l'écran, grâce à la fonction MsgBox.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Taper un caractère dans le moteur de recherche,

La boîte de dialogue du MsgBox s'affiche en effet. Elle restitue le caractère tapé avec sa longueur associée.

• Valider la boîte de dialogue,
• Taper un nouveau caractère à la suite du précédent,

Comme vous le remarquez, le MsgBox se déclenche à chaque nouvelle frappe pour proposer les informations mises à jour.

Ces données vont s'avérer précieuses pour parcourir le tableau de variables à la recherche des termes commençant par les mêmes lettres de l'alphabet, tout en excluant ceux dont la longueur est inférieure à celle de la saisie.

• Revenir dans l'éditeur de code VBA Excel,
• Passer la ligne du MsgBox en commentaire,

L'algorithme de comparaison ne doit être enclenché que s'il y a au moins un caractère réellement tapé. En effet l'événement Change peut très bien se déclencher à la suppression par un BackSpace. C'est là qu'intervient notre variable plancher, testée dans une instruction conditionnelle if.

• A la suite du code, ajouter l'instruction conditionnelle suivante :

If (nbCar > plancher) Then
prop.Visible = True
End If

Nous aurions tout aussi pu réaliser la comparaison avec le chiffre zéro sans passer par la variable. A l'intérieur de l'instruction conditionnelle if, nous affichons la zone de liste grâce à sa propriété booléenne Visible que nous recalons à True. Elle doit en effet proposer les suggestions que nous allons lui ajouter avec la suite du code.

Nous devons désormais parcourir tous les éléments contenus dans le tableau de variables. Nous connaissons ses bornes. La première valeur est rangée à l'indice zéro tandis que la dernière est située à l'indice nbMots – 1. Donc, nous devons exploiter une boucle de traitement For Next.

• Dans les bornes de l'instruction If, ajouter la boucle de traitement suivante :

For i = 0 To nbMots - 1

Next i

Pour chaque terme de recherche ainsi passé en revue, il s'agit avant d'initier la comparaison, de vérifier si la longueur est au moins égale à celle de la saisie utilisateur.

• Pour ce faire, dans les bornes de la boucle, ajouter le test suivant :

If (Len(chaine_tab(i)) > nbCar - 1) Then

End If

Comme nous l'avons vu précédemment, la fonction VBA Len permet de connaître la longueur des mots clés situés à l'indice en cours de lecture dans le tableau de variables (chaine_tab(i)). Nous comparons cette valeur à la longueur des informations saisies dans le moteur de recherche (nbCar - 1). Si le critère est vérifié, nous pouvons comparer le terme de recherche en cours de lecture avec la saisie de l'utilisateur.

• Dans les bornes de l'instruction If, ajouter le nouveau traitement conditionnel suivant :

If (saisie = Left(chaine_tab(i), nbCar)) Then
prop.AddItem chaine_tab(i)
End If

Grâce à la fonction VBA Left, nous prélevons le terme de recherche en cours de lecture (chaine_tab(i)) sur une longueur équivalente à celle de la saisie dans le moteur de recherche (nbCar). Si les deux textes sont identiques, nous ajoutons la proposition dans la zone de liste grâce à sa méthode AddItem.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Taper les premières lettres d'un terme de recherche dans la zone de saisie,

Comme vous le constatez, au fur et à mesure de la frappe, lorsque des suggestions sont jugées comme pertinentes, elles apparaissent dans la zone de liste qui s'est rendue visible à cette occasion, juste en dessous du moteur de recherche. Si vous poursuivez la saisie des caractères les propositions s'adaptent et s'affinent comme le ferait le moteur de recherche Google. Le résultat est déjà très satisfaisant à ce stade. La saisie est bien intuitive et les suggestions s'affichent en temps réel.

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

Private Sub moteur_Change()
Dim plancher As Integer: Dim i As Integer
Dim nbMots As Integer: Dim chaine_tab() As String
Dim nbCar As Integer: Dim saisie As String: Dim ligne As Integer

plancher = 0

If (chaine = "") Then
Open ThisWorkbook.Path & "\consolidation\mem_cherche.txt" For Input As #1
Line Input #1, chaine
Close #1
End If

prop.Clear
'MsgBox chaine
ligne = 6

While Cells(ligne, 2).Value <> ""
Cells(ligne, 2).Value = ""
ligne = ligne + 1
Wend

chaine_tab = Split(chaine, "|")
nbMots = UBound(chaine_tab())
saisie = moteur.Value
nbCar = Len(saisie)
'MsgBox saisie & "-" & nbCar

If (nbCar > plancher) Then
prop.Visible = True
For i = 0 To nbMots - 1
If (Len(chaine_tab(i)) > nbCar - 1) Then
If (saisie = Left(chaine_tab(i), nbCar)) Then
prop.AddItem chaine_tab(i)
End If
End If
Next i
End If
End Sub

Extraire les données d'une recherche intuitive
Nous ne souhaitons pas réaliser l'extraction complètement purgée de ses résidus Html ici. Mais nous souhaitons préparer le terrain pour le prochain volet qui sera en mesure de traiter l'information à restituer par les expressions régulières. L'objectif consiste donc à inscrire le contenu du fichier de cache dans la première cellule de la zone d'extraction. Cette inscription doit intervenir au clic dans la zone de liste après avoir validé l'une des propositions, ou encore au clic sur le bouton Ok. C'est pourquoi, nous proposons tout d'abord de créer une procédure commune.

• Revenir dans l'éditeur de code VBA Excel,
• Après la procédure moteur_change, créer la procédure validation, comme suit :

Private Sub validation()
On Error GoTo pb:

Exit Sub
pb:
Cells(6, 2).Value = "Pas de résultats dans le cache"
End Sub

Nous y prévoyons une gestion d'erreur dans le cas où les termes de recherche ne correspondent à aucun résultat prévu par les fichiers de cache. Dans ce cas, nous devrions attaquer une base de données externe, comme le ferait un code serveur et comme nous l'avait appris la formation VBA Excel pour extraire les données d'une base Access. Mais ici, nous simplifions. L'instruction On Error GoTo pb:, renvoie le code à l'étiquette pb située en bas de la procédure. Notez de même la présence de l'instruction Exit Sub juste avant l'étiquette. Dans le cas où la recherche est un succès en effet, le message relatif à la gestion d'erreur ne doit pas intervenir.

Cette procédure doit être appelée sur deux événements : au choix d'une suggestion dans la zone de liste (Au clic) et à validation sur le bouton (Au clic).

• Enregistrer les modifications et basculer sur la feuille Excel,
• Dans le ruban Développeur, cliquer sur le bouton Mode création,
• Double cliquer alors sur le bouton Ok,
• Entre les bornes de la procédure valider_Click ainsi générée, réaliser l'appel de la procédure validation, comme suit :

Private Sub valider_Click()

validation

End Sub

• Revenir sur la feuille Excel et double cliquer cette fois sur la zone de liste nommée prop,
• Entre les bornes de la procédure prop_Click ainsi générée, réaliser le même appel,

Private Sub prop_Click()

validation

End Sub

Au clic sur le bouton, nous nous contentons d'appeler la procédure validation qui se chargera de l'extraction du contenu du fichier de cache. Au clic sur l'une des propositions dans la zone de liste en revanche, nous devons d'abord inscrire les termes de recherche ainsi validés.

• Entre les bornes de la procédure prop_Click, avant l'appel de la procédure validation, ajouter la ligne de code suivante :

moteur.Value = prop.Value

Grâce aux propriétés Value des contrôles, nous affectons le contenu de la zone de texte du choix réalisé par l'utilisateur dans les propositions de la zone de liste.

• Dans la procédure validation, après l'instruction On Error Goto Pb, ajouter les déclarations de variables suivantes :

Dim chaine As String: Dim contenu As String
Dim ligne As Integer

La variable chaine permettra de stocker chaque ligne prélevée au cours de la lecture du fichier de cache. Contrairement au fichier précédent, celui-ci propose en effet plusieurs lignes. Il doit donc être parcouru par le code VBA. La variable contenu permettra de concaténer chacune de ces lignes pour reconstituer finalement l'intégralité du fichier. La variable ligne, bien entendu, doit être exploitée pour initialiser l'indice de ligne à partir duquel nous allons réaliser l'écriture dans la feuille.

Dans un premier temps, nous souhaitons masquer la zone de liste et nettoyer la zone d'extraction, comme nous l'avions fait précédemment.

• Pour cela, après les déclarations de variables, ajouter les instructions suivantes :

prop.Clear
prop.Visible = False
ligne = 6

While Cells(ligne, 2).Value <> ""
Cells(ligne, 2).Value = ""
ligne = ligne + 1
Wend

Nous vidons la zone de liste de son contenu grâce à sa méthode Clear. Nous la masquons grâce à sa propriété Visible. Ensuite, nous initialisons l'indice de ligne à 6 afin de débuter une boucle While permettant de vider chaque cellule de la colonne B tant que son contenu n'est pas vide. Il s'agit d'une réplique exacte du code précédent.

Il ne reste plus qu'à accéder au fichier de cache en mémoire, là aussi comme nous l'avons fait précédemment. Durant la lecture en revanche, une boucle Do While est nécessaire afin de récupérer le contenu ligne à ligne.

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

Open ThisWorkbook.Path & "\cache\" & Replace(moteur.Value, " ", "-") & ".txt" For Input As #1
Do While Not EOF(1)
Line Input #1, chaine
contenu = contenu & chaine
Loop
Close #1

ligne = 6
Cells(ligne, 2) = contenu

La fonction EOF associée à la boucle Do While permet de poursuivre le traitement tant que la fin du fichier, dont l'adresse mémoire lui est passée en paramètre, n'est pas atteinte. Ainsi, grâce à l'instruction Line Input, nous enregistrons chaque ligne dans la variable chaine. Et à chaque passage, nous concaténons ce fragment avec les précédents grâce à la variable contenu. Une fois le fichier fermé, nous restituons son contenu sur la ligne 6 de la colonne B.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Cliquer sur le bouton Création du ruban Développeur pour le désactiver,
• Dans la zone de texte, saisir l'information Format par exemple,
• Puis cliquer sur la proposition Format dynamique de la zone de liste,

La suggestion est bien répliquée dans le moteur de recherche et la procédure de validation est lancée. La gestion d'erreur intervient car nous n'avons placé aucun fichier de cache dans le sous dossier cache de l'application.



Pour tester le résultat finalisé, vous pouvez placer le sous dossier cache de la formation précédente sur le traitement des fichiers externes, dans le répertoire de l'application. Comme l'illustre la capture ci-dessus, l'importation reproduit strictement le contenu du fichier de cache qui consiste en un code Html pour une restitution immédiate sur les pages Web.

Nous le constaterons néanmoins dans le troisième volet dont l'objectif sera de purger ce contenu des balises Html par les expressions régulières, pilotées en VBA Excel.

Le code complet de la procédure validation pour la réception des résultats correspondant à la saisie semi-automatique, est le suivant :

Private Sub validation()
On Error GoTo pb:
Dim chaine As String: Dim contenu As String
Dim ligne As Integer

prop.Clear
prop.Visible = False
ligne = 6

While Cells(ligne, 2).Value <> ""
Cells(ligne, 2).Value = ""
ligne = ligne + 1
Wend

Open ThisWorkbook.Path & "\cache\" & Replace(moteur.Value," ", "-") & ".txt" For Input As #1
Do While Not EOF(1)
Line Input #1, chaine
contenu = contenu & chaine
Loop
Close #1

ligne = 6
Cells(ligne, 2) = contenu
Exit Sub

pb:
Cells(6, 2).Value = "Pas de résultats dans le cache"
End Sub