Extraire les données d'une base Access dans Excel

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


Sujets que vous pourriez aussi aimer :


Extraire des données de bases Access en VBA Excel

Dans ce deuxième volet des formations VBA Excel permettant d'accéder aux sources de données externes, nous proposons de finaliser l'application précédente. Nous avions reproduit le système de listes déroulantes reliées en cascade, en interrogeant une base de données Access par le code Visual Basic Excel. Nous avions prouvé à ce titre, grâce aux objets de bases de données et aux requêtes SQL, que le code était plus simple à mettre en oeuvre et que les performances étaient améliorées, notamment au niveau des temps de réponse.



Désormais, comme l'illustre la capture ci-dessus, nous souhaitons produire l'extraction des données correspondant aux choix recoupés, selon les sélections effectuées par l'utilisateur dans les listes déroulantes en cascade.

Source et présentation de l'application
Les listes déroulantes ont déjà été remplies par connexion à la base de données Access, grâce à des requêtes SQL. Les clauses Where de ces dernières ont permis de tenir compte des choix effectués dans les listes parents, afin de remplir les listes déroulantes liées en conséquence. Chacun des choix a été répercuté dans une zone de critères de la feuille Excel. En partant de ce point, nous devons créer la requête sur ces critères consolidés afin d'exécuter le code VBA Excel permettant d'attaquer la base de données Access, pour restituer les informations correspondantes.

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

Vous récoltez ainsi deux fichiers. Le premier est le classeur Excel de l'application : extraire-donnees-access.xlsm. Le second correspond à la source de données Access permettant la liaison des listes déroulantes et à partir de laquelle nous allons produire l'extraction. Ce fichier se nomme sorties.accdb.

Comme l'illustre la capture ci-dessous, cette base de données Access énumère des idées de sorties dans une table nommée societes. Chaque idée est catégorisée par département, activité et ville. Chaque champ est nommé avec le nom de la table en préfixe. Ces noms sont particulièrement importants, puisque nous devrons les spécifier dans la requête SQL. Cette dernière permettra ainsi de les atteindre pour récupérer les informations qu'ils contiennent. Bien sûr, nous devrons désigner le nom de la table et spécifier le chemin d'accès complet à la base Access, comme nous l'avons fait dans la formation permettant de créer des listes déroulantes en cascade à partir d'une source externe.



Il est temps d'accéder au classeur pour constater son fonctionnement à ce stade.

• Dans le dossier de décompression, double cliquer sur le fichier extraire-donnees-access.xlsm pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Nous arrivons sur la feuille Excel proposant à l'utilisateur d'émettre des choix par le biais de trois listes déroulantes. Ces listes sont déjà reliées entre elles par le code VBA qui exécute des requêtes SQL avec restrictions.

• Choisir un département avec la première liste déroulante, par exemple : 38-Isère,
• Choisir une activité avec la deuxième liste déroulante, par exemple : Hôtel,
• Enfin, choisir une ville avec la troisième liste déroulante, par exemple Grenoble,

Comme vous le remarquez, après avoir choisi un département, les deux autres listes déroulantes se remplissent automatiquement. Elles ne proposent que les activités et les villes recensées pour ce département dans la base de données Access. Dans le même temps, ces informations sont inscrites dans la zone de critères entre H5 et J5. Notre code VBA devra exploiter ces données pour consolider la requête SQL permettant d'interroger la base Access. Ainsi, nous pourrons restituer les résultats correspondant à la demande, dans la zone d'extraction, à partir de la ligne 9 et entre les colonne B et F.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet sur la gauche, cliquer sur l'élément Feuil1 (Formulaire),

Nous affichons ainsi le code attaché à cette feuille Excel. Nous y trouvons les trois procédures événementielles liées aux listes déroulantes en cascade. Elles permettent de déclencher l'exécution d'un code, au changement de valeur détecté, dans les listes respectives. Comme nous l'avons dit, ce code VBA se connecte à la base de données afin de remplir les listes déroulantes des informations pertinentes. Et vous remarquez l'appel de la procédure extraire à la fin de chacune d'entre elles. Cette procédure existe en effet, mais elle est vide de code pour l'instant. A chaque sélection par le biais d'une liste déroulante, les idées de sorties correspondant aux choix, doivent être restituées dans la zone d'extraction de la feuille Excel.

• Dans l'explorateur de projet, double cliquer sur l'élément Module1 cette fois,

Dans sa feuille de code, au centre de l'écran, vous notez la présence de deux procédures : nettoyer et extraire. La procédure extraire est vide en effet, tandis que le code de la procédure nettoyer est en commentaire. Nous devons réactiver ces instructions. Elles permettent en effet de réinitialiser la zone d'extraction de la feuille Excel, à chaque nouveau choix de l'utilisateur. Pour réaliser cette manipulation, nous avons besoin de la barre d'outils Edition dans l'environnement de développement.

• Cliquer sur le menu Affichage en haut de la fenêtre VBA Excel,
• Dans la liste, pointer sur Barres d'outils et cliquer sur Edition,
• Sélectionner toutes les lignes en commentaire dans la procédure nettoyer,
• Puis, cliquer sur le bouton Ne pas commenter ce bloc de la barre d'outils Edition,

En une seule action, nous avons ainsi désactivé les commentaires de toutes les lignes sélectionnées. Concrètement, les apostrophes (') en préfixe de chaque ligne ont été supprimées. La procédure nettoyer est donc de nouveau fonctionnelle.

Extraire depuis une base de données Access en VBA Excel
Dans la procédure extraire, nous allons devoir reconstituer la clause WHERE de la requête en fonction des éléments inscrits dans la zone de critères de la feuille Excel. Puis, nous exécuterons cette requête sur la base Access grâce aux objets VBA de base de données. Souvenez-vous, pour piloter ces objets, nous avions ajouter la référence essentielle au projet : Microsoft Office 16.0 Access database engine. Comme toujours, nous débutons par la déclaration et l'affectation des variables nécessaires au traitement.

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

Dim critere As String
Dim chemin_bd As String: Dim ligne As Integer
Dim enr As Recordset: Dim base As Database

chemin_bd = ThisWorkbook.Path & "\sorties.accdb"
ligne = 9

La variable critere est déclarée comme un String, soit une chaîne de caractère. Elle servira à stocker la syntaxe de la clause Where pour la concaténation à suivre, afin de reconstituer la requête Sql globale. La variable chemin_bd typée comme un String elle aussi, devra mémoriser le chemin d'accès complet à la base de données, pour pouvoir la désigner par le code. C'est pourquoi nous l'affectons ensuite. La propriété Path de l'objet ThisWorkbook retourne le chemin d'accès au classeur actif. Nous concaténons ce chemin au nom du fichier de base de données. La variable ligne permettra de parcourir toutes les lignes de la zone d'extraction, pour restituer chaque enregistrement correspondant à la suite. C'est pourquoi nous l'affectons ensuite à l'indice 9, soit la première ligne de la zone d'extraction. Enfin, nous déclarons les objets nécessaires à la manipulation des données. La variable base est déclarée comme un objet Database pour justement prendre le contrôle de la base de données Access. La variable enr est déclarée comme un objet Recordset. Elle permettra donc de manipuler les enregistrements résultant de la requête sur la base de données. Encore une fois, ces deux dernières déclarations ont été rendues possibles, grâce à l'ajout de la référence citée plus haut, dans le projet VBA Excel.

Afin d'exécuter le code permettant de questionner la base de données, il est nécessaire de vérifier qu'au moins un critère est spécifié. Si ce n'est pas le cas, bien entendu, aucun traitement ne doit être lancé.

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

If (Range("H5").Value = "" And Range("I5").Value = "" And Range("J5").Value = "") Then
Return
End If

La clause And de l'instruction conditionnelle If permet de vérifier tous ces critères ensemble. L'instruction Return ordonne au programme d'abandonner l'exécution lorsque toutes les cellules de la zone de critères sont vides.

Il s'agit maintenant de reconstituer la clause Where de la requête Sql. Quatre cas sont possibles.

• Seule H5 est définie : Le critère doit être vérifié sur le département,
• Seules H5 et I5 sont définies : Le critère doit recouper le département et l'activité,
• Seules H5 et J5 sont définies : Le critère doit recouper le département et la ville,
• Toutes les cellules sont définies : Le critère doit recouper le département, l'activité et la ville,

Comme précédemment, une instruction If avec des clauses And permettra de vérifier ces conditions à recouper.

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

nettoyer
If (Range("H5").Value <> "" And Range("I5").Value = "" And Range("J5").Value = "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "'"
ElseIf (Range("H5").Value <> "" And Range("I5").Value = "" And Range("J5").Value <> "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "' AND societes_ville = '" & Range("J5").Value & "'"
ElseIf (Range("H5").Value <> "" And Range("I5").Value <> "" And Range("J5").Value = "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "' AND societes_activite = '" & Range("I5").Value & "'"
ElseIf (Range("H5").Value <> "" And Range("I5").Value <> "" And Range("J5").Value <> "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "' AND societes_activite = '" & Range("I5").Value & "' AND societes_ville = '" & Range("J5").Value & "'"
End If

Tout d'abord, nous appelons la procédure nettoyer. Comme une nouvelle extraction doit survenir, les résultats de l'ancienne doivent être purgés. Ensuite, nous transcrivons ce que nous avons énoncé plus haut afin de reconstruire la clause Where de la requête Sql, selon les éléments présents dans la zone de critères. La dernière branche ElseIf par exemple vérifie que les trois critères sont bien énoncés. Elle reconstitue donc la syntaxe en débutant par le mot clé Where. L'égalité est alors écrite entre chaque champ correspondant à la cellule du critère. Par exemple : societes_departement= '" & Range("H5").Value & "', doit faire la correspondance entre l'information présente dans le champ societes_departement de la base de données Access et le département effectivement inscrit en cellule H5 de la feuille Excel. Dans la concaténation, notez la présence des simples côtes, avant et après les doubles côtes. L'information dynamique du département est en effet textuelle et doit être encadrée dans la syntaxe Sql.

Nous devons désormais initialiser nos objets de base de données.

• Pour ce faire, à la suite du code, ajouter les deux initialisations suivantes :

Set base = DBEngine.OpenDatabase(chemin_bd)
Set enr = base.OpenRecordset("SELECT * FROM societes" & critere, dbOpenDynaset)

La méthode OpenDatabase de l'objet DBEngine permet de créer l'objet VBA permettant de piloter la base de données, dont le chemin d'accès lui est passé en paramètre. Dès lors, la variable base hérite des propriétés et méthodes permettant de contrôler cette base. C'est pourquoi dans la foulée, nous exploitons sa méthode OpenRecordset afin de créer l'objet enr. Dès lors, l'objet enr est capable de piloter les enregistrements de la base de données selon la syntaxe de la requête Sql qui est passée en paramètre de la méthode OpenRecordset. Cette requête est reconstituée par concaténation. La première partie : SELECT * FROM societes, indique de sélectionner tous les champs (*) de la table societes pour la base sorties.accdb. Comme nous devons faire correspondre cette sélection avec les choix de l'utilisateur, nous concaténons son expression avec la clause Where reconstruite dans la variable critere.

Tout est prêt désormais pour accéder aux enregistrements résultants et récupérer les informations de champs, afin de les restituer dans la zone d'extraction de la feuille Excel.

• En conséquence, à la suite du code VBA, ajouter les instructions suivantes :

enr.MoveFirst
Do
Cells(ligne, 2).Value = enr.Fields("societes_id").Value
Cells(ligne, 3).Value = Replace(enr.Fields("societes_nom").Value, "#", "'")
Cells(ligne, 4).Value = Replace(enr.Fields("societes_activite").Value, "#", "'")
Cells(ligne, 5).Value = Replace(enr.Fields("societes_departement").Value, "#", "'")
Cells(ligne, 6).Value = Replace(enr.Fields("societes_ville").Value, "#", "'")
ligne = ligne + 1
enr.MoveNext
Loop Until enr.EOF = True

Comme nous l'avait appris la formation VBA Access sur la connexion aux données, la méthode MoveFirst de l'objet Recordset, permet de placer le pointeur de lecture sur le premier enregistrement résultant. Comme nous devons les parcourir tous, nous initions une boucle Do, pour un traitement itératif. La propriété EOF de l'objet Recordset, réglée à True (Loop Until enr.EOF = True), indique à la boucle de poursuivre le traitement jusqu'au dernier enregistrement. A chaque passage dans la boucle, nous récupérons les informations des champs pour l'enregistrement en cours, grâce à la propriété Fields de l'objet Recordset. Le nom du champ de base de données lui est passé en paramètre (enr.Fields("societes_id").Value). Et nous affectons cette information dans la cellule dédiée de la zone d'extraction grâce à la propriété Value de l'objet Cells (Cells(ligne, 2).Value). Vous constatez l'emploi de la fonction VBA Replace pour effectuer un traitement à la volée. Ce traitement consiste à remplacer le symbole # par une apostrophe (') dans les données récupérées. C'est ainsi qu'elles sont inscrites en effet, dans la base de données.

Enfin, nous n'oublions d'incrémenter les variables de boucle. La variable ligne doit être déplacée sur la rangée suivante (ligne = ligne + 1), afin de poursuivre la restitution de l'extraction à la suite. Le pointeur de lecture doit être placé sur l'enregistrement suivant grâce à la méthode MoveNext de l'objet Recordset (enr.MoveNext).

Comme nous le disons à chaque formation, toute connexion ouverte à une base de données doit être fermée.

• Pour terminer la procédure d'extraction, ajouter les quatre instructions suivantes :

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing

La méthode Close des objets de base de données permet de terminer les connexions. Puis la réaffectation des variables à Nothing permet de les purger de la mémoire pour libérer les ressources.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Sélectionner un département à l'aide de la première liste déroulante,

Instantanément, toutes les informations correspondantes sont extraites de la base de données par le code VBA Excel qui exécute la requête Sql, grâce aux objets de base de données. La clause Where dans ce cas précis, correspond à la première condition dans notre code, celle qui constate que seule la cellule H5 du département est définie :

critere = " WHERE societes_departement = '" & Range("H5").Value & "'"

• Sélectionner une activité à l'aide de la deuxième liste déroulante,

Les résultats de l'extraction s'affinent instantanément et remplacent les anciens en lieu et place. Désormais, ne sont proposées que les idées de sorties qui correspondent au département et à l'activité. Comme vous le constatez, la communication avec la base de données Access est excessivement rapide, plus rapide qu'avec des données disposées dans une feuille Excel. Nous soulignons et constatons une fois encore l'importance d'exploiter les logiciels dédiés dans leur domaine. Et Access est LE gestionnaire de base de données.

• Sélectionner une ville à l'aide de la dernière liste déroulante,

Instantanément les résultats s'affinent encore pour tomber à la précision du troisième critère recoupé. Notre application Excel pour extraire les informations depuis une base de données Access par le code VBA, fonctionne donc parfaitement.



Dans l'exemple de la capture ci-dessus, nous avons produit une extraction particulière ne recoupant que la ville et le département. C'est une option que nous avons en effet offerte par le code et qui correspond à la première branche ElseIf dans la construction de la clause WHERE :

critere = " WHERE societes_departement = '" & Range("H5").Value & "' AND societes_ville = '" & Range("J5").Value & "'"

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

Sub extraire()
Dim critere As String
Dim chemin_bd As String: Dim ligne As Integer
Dim enr As Recordset: Dim base As Database

chemin_bd = ThisWorkbook.Path & "\sorties.accdb"
ligne = 9

If (Range("H5").Value = "" And Range("I5").Value = "" And Range("J5").Value = "") Then
Return
End If

nettoyer
If (Range("H5").Value <> "" And Range("I5").Value = "" And Range("J5").Value = "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "'"
ElseIf (Range("H5").Value <> "" And Range("I5").Value = "" And Range("J5").Value <> "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "' AND societes_ville = '" & Range("J5").Value & "'"
ElseIf (Range("H5").Value <> "" And Range("I5").Value <> "" And Range("J5").Value = "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "' AND societes_activite = '" & Range("I5").Value & "'"
ElseIf (Range("H5").Value <> "" And Range("I5").Value <> "" And Range("J5").Value <> "") Then
critere = " WHERE societes_departement = '" & Range("H5").Value & "' AND societes_activite = '" & Range("I5").Value & "' AND societes_ville = '" & Range("J5").Value & "'"
End If

Set base = DBEngine.OpenDatabase(chemin_bd)
Set enr = base.OpenRecordset("SELECT * FROM societes" & critere, dbOpenDynaset)

enr.MoveFirst
Do
Cells(ligne, 2).Value = enr.Fields("societes_id").Value
Cells(ligne, 3).Value = Replace(enr.Fields("societes_nom").Value, "#", "'")
Cells(ligne, 4).Value = Replace(enr.Fields("societes_activite").Value, "#", "'")
Cells(ligne, 5).Value = Replace(enr.Fields("societes_departement").Value, "#", "'")
Cells(ligne, 6).Value = Replace(enr.Fields("societes_ville").Value, "#", "'")
ligne = ligne + 1
enr.MoveNext
Loop Until enr.EOF = True

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing
End Sub