Archiver les résultats Excel en base de données Access

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


Sujets que vous pourriez aussi aimer :


Archiver et classer les résultats en VBA Excel

Dans ce dernier volet des formations consistant à monter l'application Excel destinée à évaluer les candidats sur des QCM stockés en base de données externe, il nous reste à archiver les résultats du test. De plus, nous les exploiterons pour fournir le classement du candidat à l'issue de l'épreuve.



Sources et présentation de la problématique
Pour participer à une évaluation, un candidat doit pouvoir s'inscrire ou s'identifier. Il s'agit des travaux que nous avons développés dans les formations précédentes. Nous devons poursuivre la conception sur la base de ces acquis.

• Télécharger le fichier archiver-classer-donnees.rar en cliquant sur son lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous le constatez, la décompression fournit deux fichiers. Il y a tout d'abord la base de données Access archivant toutes les données des candidats et offrant les questionnaires. Cette base de données se nomme : questionnaires-evaluations.accdb. Et puis il y a le fichier Excel de l'application d'évaluation nommé : archiver-classer-donnees.xlsm.

• Double cliquer sur le fichier archiver-classer-donnees.xlsm pour l'ouvrir dans Excel,
• Cliquer ensuite sur le bouton Activer la modification du bandeau de sécurité,

Le formulaire d'identification se charge automatiquement à l'ouverture du classeur. A la fin des animations destinées à attirer l'attention du candidat, ce dernier peut saisir son identifiant. S'il est reconnu, il ne lui reste plus qu'à choisir un thème dans la liste déroulante et à cliquer sur le bouton Démarrer pour débuter le test.

Dès lors, le focus est rendu à la feuille Evaluations sur laquelle sont générées les questions aléatoires. Le candidat émet une proposition de réponse grâce à une liste déroulante et clique sur le bouton Suivant pour progresser dans l'évaluation. Son score est incrémenté à chaque bonne réponse jusqu'à la vingtième question qui doit mettre fin au test. C'est à partir de ce point que nous devons reprendre le développement.

Sa note et son temps de parcours son archivés en variables publiques. Le nom du questionnaire et les informations du candidat sont quant à eux stockés dans la feuille Session. Ce sont toutes ces données que nous devons consolider pour les inscrire dans la table resultats de la base de données. Il s'agit ensuite d'en récupérer les informations, triées dans l'ordre décroissant, pour produire le tableau des scores avec le classement du candidat.

• Fermer le formulaire d'identification en cliquant sur la croix de sa fenêtre,
• Puis, cliquer sur l'onglet Session en bas de la fenêtre Excel pour afficher sa feuille,

Vous y notez la présence des informations stockées du dernier candidat ayant passé une évaluation.

Archiver les résultats en base de données externe
Souvenez-vous, dans la formation précédente, nous avions développé le code de la procédure attachée au bouton Suivant. C'est elle qui permet de dérouler les questions aléatoires de l'évaluation en comptabilisant le score.

• Basculer dans l'éditeur de code VBA Excel avec le raccourci clavier ALT + F11,
• Dans l'explorateur de projet sur la gauche de la fenêtre, déployer le dossier Modules,
• Double cliquer sur l'élément Module1 pour afficher sa feuille de code au centre de l'écran,

Vous notez la présence de quelques procédures de codes programmées lors des formations précédentes. Parmi elles se trouve la procédure suivant. Elle appelle la procédure fin lorsque les 20 questions sont atteintes. Mais nous l'avions mise en commentaire. En effet, c'est elle que nous devons développer dans cette dernière étape.

• Sous la procédure suivant, créer la procédure fin, comme suit :

Sub fin()

End Sub

Des variables sont nécessaires notamment pour manipuler les enregistrements de la base de données. Nous devons y inscrire des informations et en récupérer certaines.

• Dans les bornes de la procédure fin, ajouter les déclarations de variables suivantes :

Dim requete As String: Dim chemin_bd As String
Dim enr As Recordset: Dim base As Database
Dim duree: Dim ligne As Integer

Nous retrouvons tout d'abord les déclarations classiques. La variable requete doit mémoriser la syntaxe des requêtes SQL à exécuter sur la base de données. C'est pourquoi nous déclarons la variable chemin_bd afin de stocker le chemin d'accès à cette dernière. De fait, les variables enr et base sont déclarées pour respectivement manipuler les enregistrements et pointer sur la base de données. Ces deux déclarations sont rendues possibles grâce à la présence de la référence à la librairie Microsoft Office access database engine. C'est ainsi que nous instancierons la classe externe permettant d'hériter des méthodes pour manipuler la base de données. La variable duree doit prélever le temps afin de calculer la durée de l'évaluation. Ce même prélèvement avait en effet été réalisé au démarrage du test, au clic sur le bouton Démarrer du formulaire d'identification. Enfin la variable ligne servira à pointer sur les lignes de la feuille Classements afin de restituer les résultats sur le thème choisi.

Il s'agit désormais de réaliser les premières affectations.

• A la suite du code de la procédure, ajouter les instructions VBA suivantes :

duree = Int(Timer - temps)
chemin_bd = ThisWorkbook.Path & "\questionnaires-evaluations.accdb"
Sheets("Classements").Select

La variable publique temps avait mémorisé le moment de départ du test. La fonction VBA Timer renvoie le temps qu'il est à la milliseconde près. La différence entre les deux, convertie en entier (Fonction Int), indique la durée de parcours en secondes. La propriété Path de l'objet VBA Excel ThisWorkbook fournit le chemin d'accès au classeur actif. Comme la base de données est située dans le même dossier, nous concaténons son nom à ce chemin. Nous pourrons dès lors la désigner pour initialiser les objets de bases de données. Puis, grâce à la méthode Select de l'objet VBA Excel Sheets, nous affichons la feuille Classements. A l'issue de la partie, nous devons en effet basculer automatiquement l'affichage sur le tableau des scores.

Les objets de base de données doivent désormais être initialisés. Il s'agit dans un premier temps d'inscrire les résultats du candidat dans la table resultats de la base Access.



La figure ci-dessus donne un aperçu de cette table. Vous pouvez l'afficher en ouvrant la base de données située à la racine du dossier de décompression.

• A la suite du code de la procédure fin, ajouter les deux affectations suivantes :

Set base = DBEngine.OpenDatabase(chemin_bd)

requete = "INSERT INTO resultats (res_id, res_nom, res_note, res_duree) VALUES ('" & Sheets("Session").Range("C4").Value & "','" & Sheets("Session").Range("C5").Value & "'," & Sheets("Evaluations").Range("G11").Value & "," & duree & ")"
base.Execute requete

Il s'agit désormais de techniques bien connues que nous avons mise en oeuvre tout au long de ces formations pour monter cette application d'évaluation. Tout d'abord, la méthode OpenDatabase de l'objet DBEngine permet de pointer sur la base de données dont le chemin lui est passé en paramètre. De fait, l'objet base hérite des méthodes permettant de manipuler la base de données ainsi désignée. Nous enregistrons la syntaxe de la requête action dans la variable requete. Elle consiste à insérer (INSERT INTO) dans les champs énumérés (res_id,res_nom...) de la table resultats, toutes les données mémorisées du candidat et de ses résultats.

Ces données sont des variables tantôt mémorisées dans la feuille Session et dans la feuille Evaluations et tantôt stockées en variable publique. Lorsqu'il s'agit d'informations textuelles, ces données doivent être encadrées de côtes. C'est pourquoi nous ajoutons des simples côtes dans la syntaxe SQL elle-même encadrée de doubles côtes : '" & Sheets("Session").Range("C4").Value & "'...

Enfin, la méthode Execute de l'objet base permet d'exécuter la requête qui lui est passée en paramètre. A ce stade, nous pouvons donc considérer que les données du candidat sont archivées en base de données.

Dans la foulée, il s'agit de récupérer tous les résultats pour le test en cours. Mais comme seul l'identifiant de l'utilisateur est inscrit dans la table resultat, du fait des relations entre les tables, nous devons exécuter une requête multitable. Ainsi, pour chaque candidat, en même temps que ses résultats, nous récupèrerons ses données personnelles, comme son nom et son prénom.

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

requete = "SELECT * FROM msy_inscrits INNER JOIN resultats ON msy_inscrits.inscrit_identifiant = resultats.res_id WHERE resultats.res_nom ='" & Sheets("Session").Range("C5").Value & "' ORDER BY res_note DESC,res_duree DESC, res_date DESC"
Set enr = base.OpenRecordset(requete, dbOpenDynaset)

Nous sélectionnons tous les champs (SELECT *) des deux tables msy_inscrits et resultats liées (INNER JOIN) par la clé primaire (ON msy_inscrits.inscrit_identifiant = resultats.res_id) pour lesquels le nom du questionnaire est celui choisi par le candidat (WHERE resultats.res_nom ='" & Sheets("Session").Range("C5").Value & "'). Nous restituons ces données triées (ORDER BY) par ordre décroissant sur la note, puis la durée et la date. C'est alors la méthode héritée OpenRecordset de l'objet enr qui permet d'exécuter cette requête SQL.

Avant de restituer les données sélectionnées par la requête, nous devons supprimer les anciennes informations présentes sur la feuille Classements à partir de la ligne 5.

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

If (enr.RecordCount > 0) Then

ligne = 5
While (Cells(ligne, 2).Value <> "")

Cells(ligne, 2).EntireRow.Delete

Wend

End If

Comme toujours, grâce à la propriété RecordCount de l'objet Recordset, nous nous assurons tout d'abord que la requête retourne bien des enregistrements (>0). Nous initialisons la variable ligne sur la première rangée du tableau des classements.

Nous réalisons ensuite un traitement récursif (boucle While) pour nettoyer le tableau. Le critère consiste à poursuivre les actions tant que la cellule de la colonne B pour la ligne en cours n'est pas vide (Cells(ligne, 2).Value <> ""). Ce traitement consiste à supprimer l'intégralité de la ligne pour purger le tableau des anciens scores (Cells(ligne, 2).EntireRow.Delete). Notez que nous n'incrémentons pas la variable ligne à chaque passage dans la boucle. En effet, la suppression d'une ligne complète sélectionne naturellement la suivante. Si nous l'avions incrémentée, une ligne sur deux aurait été sautée.

Désormais, il est temps de restituer la sélection proposée par la requête SQL. Il s'agit de placer le pointeur de lecture sur le tout premier enregistrement puis de les parcourir tous grâce à une boucle. A chaque passage, nous devons restituer le nom et le prénom du candidat, la date de l'évaluation, la note obtenue et le temps de parcours.

• Dans l'instruction conditionnelle If, après la boucle While, ajouter les instructions suivantes :

enr.MoveFirst
'ligne = 5

Do
Cells(ligne, 2).Value = enr.Fields("inscrit_nom").Value
Cells(ligne, 3).Value = enr.Fields("inscrit_prenom").Value
Cells(ligne, 4).Value = enr.Fields("res_date").Value
Cells(ligne, 5).Value = enr.Fields("res_note").Value
Cells(ligne, 6).Value = enr.Fields("res_duree").Value

ligne = ligne + 1
enr.MoveNext
Loop Until enr.EOF = True

La méthode MoveFirst de l'objet Recordset place le pointeur de lecture sur le premier enregistrement. A partir de là, nous engageons une boucle de lecture (Do). Elle consiste à parcourir tous les enregistrements jusqu'au dernier (Until enr.EOF = True). Pour chacun, nous restituons l'information de champ (enr.Fields("inscrit_nom").Value), dans la cellule correspondante de la feuille Classements (Cells(ligne, 2).Value). Pour traiter l'enregistrement suivant, nous n'oublions de déplacer le pointeur de lecture grâce à la méthode MoveNext de l'objet Recordset. De même, nous incrémentons la variable ligne afin d'inscrire le résultat suivant sur la ligne du dessous dans la feuille Classements.

Il ne nous reste plus qu'à libérer les ressources.

• Après la fermeture de l'instruction conditionnelle (End If), ajouter le code VBA suivant :

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing

La méthode Close des objets de base de données ferme les connexions. La réaffectation à Nothing permet de les détruire pour les décharger de la mémoire de l'ordinateur.

Il est temps de réaliser une simulation complète.

• Dans la procédure Suivant, supprimer l'apostrophe devant l'appel de la procédure fin,

La procédure Suivant est déclenchée à chaque fois que le candidat clique sur le bouton Suivant de la feuille Evaluations pour valider une réponse. De fait, la procédure fin est susceptible d'être appelée lorsque la dernière question est atteinte (If(nb_rest = 0) Then). C'est elle qui doit récupérer les résultats pour les offrir en basculant l'affichage sur la feuille Classements.

• Enregistrer les modifications (CTRL + S),
• Fermer l'éditeur VBA ainsi que le classeur Excel,
• Puis le rouvrir en double cliquant sur son nom depuis le dossier de décompression,
• Taper un identifiant valide, par exemple : yh313,
• Choisir un thème dans la liste déroulante, par exemple : Anglais niveau 1,
• Cliquer sur le bouton Démarrer pour débuter l'évaluation,
• Passer le test complet en émettant des propositions grâce à la liste déroulante située en C11 puis en cliquant sur le bouton Suivant jusqu'à la dernière question,

Comme vous le constatez, les questions s'enchaînent, le score et l'indication sur le nombre de questions restantes sont mis à jour. Il s'agit du développement que nous avons produit lors de la formation précédente. Ces données sont stockées en variables publiques tandis que les informations du candidat sont archivées dans la feuille Session.

Et c'est ainsi que la feuille Classements est affichée à l'issue de la partie. La procédure fin est appelée détectant que les 20 questions ont été déroulées. Elles exploitent ces données stockées pour les insérer dans la table resultats de la base de données Access. De fait, sa requête sélection restitue ensuite les résultats incluant ceux, tout frais, du candidat.

C'est ce qu'illustre la capture ci-dessous. Bien sûr, ce tableau des scores s'enrichira au fil des évaluations passées par les candidats inscrits.



Si vous ouvrez la table resultats de la base de données Access, vous constatez en effet la présence de la nouvelle ligne des résultats consolidés pour le candidat ayant abouti l'évaluation.



Enfin, vous constatez que le candidat est automatiquement repéré dans le tableau des classements. Cet indicateur visuel dynamique est l'oeuvre d'une mise en forme conditionnelle préréglée sur la feuille. C'est ce qu'illustre la capture ci-dessous. Si le nom, le prénom et la date archivés dans la feuille Session coïncident, alors la ligne entière doit apparaître avec une couleur de remplissage explicitement différente.



Nous en avons terminé avec le développement de l'application d'évaluation VBA Excel sur base de données Access. Des améliorations notamment ergonomiques peuvent être apportées. Mais le moule est donné et l'application est parfaitement fonctionnelle.

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

Sub fin()
Dim requete As String: Dim chemin_bd As String
Dim enr As Recordset: Dim base As Database
Dim duree: Dim ligne As Integer

duree = Int(Timer - temps)
chemin_bd = ThisWorkbook.Path & "\questionnaires-evaluations.accdb"
Sheets("Classements").Select

Set base = DBEngine.OpenDatabase(chemin_bd)

requete = "INSERT INTO resultats (res_id, res_nom, res_note, res_duree) VALUES ('" & Sheets("Session").Range("C4").Value & "','" & Sheets("Session").Range("C5").Value & "'," & Sheets("Evaluations").Range("G11").Value & "," & duree & ")"
base.Execute requete

requete = "SELECT * FROM msy_inscrits INNER JOIN resultats ON msy_inscrits.inscrit_identifiant = resultats.res_id WHERE resultats.res_nom ='" & Sheets("Session").Range("C5").Value & "' ORDER BY res_note DESC,res_duree DESC, res_date DESC"
Set enr = base.OpenRecordset(requete, dbOpenDynaset)

If (enr.RecordCount > 0) Then

ligne = 5
While (Cells(ligne, 2).Value <> "")

Cells(ligne, 2).EntireRow.Delete

Wend

enr.MoveFirst

Do
Cells(ligne, 2).Value = enr.Fields("inscrit_nom").Value
Cells(ligne, 3).Value = enr.Fields("inscrit_prenom").Value
Cells(ligne, 4).Value = enr.Fields("res_date").Value
Cells(ligne, 5).Value = enr.Fields("res_note").Value
Cells(ligne, 6).Value = enr.Fields("res_duree").Value

ligne = ligne + 1
enr.MoveNext
Loop Until enr.EOF = True

End If

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing

End Sub