Exporter les données de tables Access dans Excel en VBA

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


Sujets que vous pourriez aussi aimer :


Exporter les données Access dans Excel

A l'occasion de volets précédents, nous avons découvert quelques techniques d'exportation des données de tables Access dans des fichiers externes. Ici, nous nous intéressons à la communication avec Excel. Dans un premier temps, il est question d'exporter l'intégralité des données d'une table Access dans un nouveau classeur Excel à sauvegarder sur le disque. Nous pousserons ensuite ces travaux pour apprendre à consolider les informations, en exportant les données d'autres tables à la suite des données déjà exportées dans ce même fichier Excel.

Base de données Access à télécharger
Pour aboutir ces travaux, nous suggérons d'appuyer l'étude sur une base de données Access consolidant des codes VBA d'astuces précédentes, notamment pour parcourir les enregistrements et les champs d'une table.

• Télécharger le fichier compressé exporter-access-dans-excel.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous pouvez le voir, la décompression livre le fichier de la base de données accompagné d'un sous dossier nommé export. Ce dernier est vide pour l'instant. C'est lui qui doit accueillir notre création Excel.

• Double cliquer sur le fichier de la base de données pour l'ouvrir dans Access,
• Cliquer alors sur le bouton Activer le contenu du bandeau de sécurité,
• Dans le volet de navigation, double cliquer sur le formulaire fExport pour l'ouvrir,

Il offre une liste déroulante permettant de choisir l'une des tables de la base de données, à exporter.



Découvrir le code VBA
Avant de nous lancer dans la mise en oeuvre de l'interopérabilité qu'offre les logiciels de la gamme Office, nous proposons de survoler rapidement le code VBA Access déjà en place. Rapidement, car il s'agit de briques assemblées à partir des astuces que nous avons démontrées à l'occasion des volets précédents.

• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir le mode Création,
• Sur le formulaire en conception, cliquer sur le bouton Exporter pour le sélectionner,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer ensuite sur le petit bouton associé à son événement Au clic,

De cette manière, nous basculons directement dans l'éditeur VBA Access, entre les bornes de la procédure événementielle exporter_Click.

Le chemin d'accès pour la sauvegarde de l'exportation Excel est tout d'abord construit dans le sous dossier export du répertoire de l'application locale :

nomF = CurrentProject.Path & "\export\donnees.xlsx"

Les objets de base de données pour piloter la base, ses tables et ses enregistrements sont ensuite initialisés sur la base de données en cours :

Set base = CurrentDb()
Set enr = base.OpenRecordset(nomTable)
Set table = base.TableDefs(nomTable)

Tous les noms de champs de la table désignée sont stockés dans un tableau de variables. C'est ainsi que nous pourrons les parcourir tous pour chaque enregistrement à extraire et à reconstituer dans les cellules de la feuille Excel :

compteC = table.Fields.Count
ReDim champs(compteC)

For Each champ In table.Fields
champs(compteur) = champ.Name
compteur = compteur + 1
Next

Dès lors, intervient la boucle consistant à parcourir tous les enregistrements de la table. C'est dans cette boucle que nous devrons récolter les informations de chaque champ pour les exporter dans Excel :

enr.MoveFirst
Do
enr.MoveNext
Loop Until enr.EOF

Les variables supplémentaires
Pour exploiter le code existant et être en mesure d'exporter les informations des champs de chaque enregistrement de la table vers Excel, nous devons commencer par piloter une instance de son application. Donc, une variable dédiée doit tout d'abord être ajoutée dans la partie déclarative.

• Dans la partie déclarative, ajouter les deux déclarations de variables suivantes :

Private Sub exporter_Click()
Dim base As Database: Dim enr As Recordset
Dim nomTable As String: Dim compteur As Integer
Dim nomF As String: Dim champs() As String
Dim compteC As Byte: Dim table As TableDef: Dim champ As Field
Dim instanceE As Object: Dim compteL As Integer

If (listeTables.Value <> "") Then
...

Nous déclarons la variable instanceE comme un objet au sens large (Object). C'est elle qui doit instancier la classe permettant d'hériter des propriétés et méthodes pour piloter un nouveau classeur Excel. Nous déclarons la variable compteL comme un entier classique. Son rôle est de suivre la progression de la lecture des enregistrements de la table pour pointer à chaque fois sur une ligne suivante dans la feuille du classeur Excel.

Créer une instance d'Excel depuis Access
Nous en avons l'habitude désormais, c'est la fonction VBA CreateObject qui permet d'instancier une classe en fonction de l'information qui lui est passée en paramètre.

• Sous les initialisations des variables de base de données, ajouter les instructions suivantes :

...
Set base = CurrentDb()
Set enr = base.OpenRecordset(nomTable)
Set table = base.TableDefs(nomTable)
Set instanceE = CreateObject("Excel.Application")
instanceE.Visible = False
instanceE.Workbooks.Add
compteL = 1
compteur = 0
...

Grâce à la valeur Excel.Application passée en paramètre de la fonction CreateObject, notre objet instanceE hérite des propriétés et méthodes pour piloter une nouvelle instance d'Excel. C'est ce qu'elle fait dans l'enchaînement avec la propriété Visible et la méthode Add de la collection Workbooks. La propriété permet de faire en sorte que le traitement se réalise en tâche de fond, donc sans qu'aucune fenêtre d'Excel ne s'ouvre. La méthode crée tout simplement un nouveau classeur que nous allons manipuler en mémoire. Enfin, la variable compteL est initialisée sur la première ligne de la feuille.

Inscrire les entêtes dans la feuille Excel
Avant d'extraire les enregistrements vers Excel, il apparaît opportun de reproduire les noms de champs en entêtes de colonne sur la feuille du nouveau classeur Excel. Pour cela, nous devons intervenir dans la boucle For Each parcourant tous les champs de la table désignée et les stockant dans un tableau de variables.

• Dans la boucle For Each, ajouter l'instruction VBA suivante :

...
For Each champ In table.Fields
champs(compteur) = champ.Name
instanceE.Sheets(1).Cells(compteL, compteur +1).Value = champ.Name
compteur = compteur + 1
Next
...

Grâce à notre instance, nous accédons à la première feuille (Sheets(1)) pour atteindre ses cellules avec l'objet Cells. Nous réalisons l'inscription de chaque nom de champ sur la première ligne (compteL). Chacun de ces noms est écrit dans la colonne suivante (compteur + 1) puisque cette variable est incrémentée naturellement par la boucle à chaque nouveau champ passé en revue. Et c'est bien sûr, la propriété Name de l'objet champ qui renvoie l'information sur le nom du champ en cours d'analyse.

Extraire chaque enregistrement dans Excel
Maintenant pour extraire le détail de chaque enregistrement sous la ligne d'entête et les uns en dessous des autres, nous devons commencer par incrémenter la variable compteL, pour la faire pointer sur la deuxième ligne de la feuille. Ensuite, nous devons intervenir dans la boucle parcourant les enregistrements, pour récolter toutes les données de leurs champs et les exporter dans les colonnes dédiées.

• Au-dessus et dans la boucle Do, ajouter les instructions VBA suivantes :

...
compteL = 2
enr.MoveFirst
Do
For compteur = 0 To compteC - 1
instanceE.Sheets(1).Cells(compteL, compteur + 1).Value = enr.Fields(champs(compteur)).Value
Next compteur
compteL = compteL + 1
enr.MoveNext
Loop Until enr.EOF
...

Comme nous l'annoncions, nous commençons par recaler la variable compteL sur le bon indice de ligne. Ensuite et pour chaque enregistrement, nous engageons une boucle parcourant le tableau de variables des noms de champs. Pour la ligne en cours (compteL) et pour la colonne en cours (compteur + 1), nous inscrivons l'information contenue dans le champ de l'enregistrement en cours :

(enr.Fields(champs(compteur)).Value)

La variable de ligne compteL est effectivement naturellement incrémentée à chaque enregistrement suivant (compteL = compteL +1). La variable de colonne compteur est naturellement incrémentée à chaque champ suivant (For compteur = 0 To compteC - 1).

Enregistrer le classeur des exportations
Une fois tous les enregistrements extraits, il ne nous reste plus qu'à sauvegarder le classeur et détruire l'objet de l'instance Excel pour libérer proprement la mémoire.

• Sous la boucle Do, ajouter les instructions VBA suivantes :

enr.MoveNext
Loop Until enr.EOF

instanceE.ActiveWorkbook.SaveAs nomF
instanceE.Quit
Set instanceE = Nothing
MsgBox "l'exportation de la table dans Excel est terminée.", vbInformation

enr.Close
base.Close

Nous exploitons simplement la méthode SaveAs de l'objet Workbook pour procéder à l'enregistrement du classeur sous le nom stocké dans la variable nomF. Enfin, nous exploitons la fonction VBA MsgBox pour déclencher une boîte de dialogue et indiquer à l'utilisateur que l'exportation est terminée, donc que le fichier Excel est prêt.

Exportation des données dans Excel
Il est temps de tester ces ajustements du code VBA.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• Exécuter ce dernier avec la touche F5 du clavier par exemple,
• Choisir un nom de table avec la liste déroulante,
• Puis, cliquer sur le bouton Exporter,

La boîte de dialogue se déclenche bien à l'issue du traitement.

• Cliquer sur le bouton Ok pour la fermer,
• Dans l'explorateur Windows, ouvrir le sous dossier export,

Comme vous pouvez l'apprécier, le fichier donnees.xlsx est bien présent.

• Double cliquer sur ce dernier pour l'ouvrir dans Excel,

Et là, nous découvrons tous les enregistrements de la table désignée, parfaitement reconstitués les uns sous les autres et avec la ligne d'entête pour les noms de champs. Certes, il convient d'adapter la largeur des colonnes par un simple double clic aux intersections des étiquettes, comme vous le savez. Et tout de suite, les informations apparaissent plus lisibles et plus structurées.



Bref, notre exportation automatisée des données Access dans Excel par le code VBA est un succès.