Consolider les données 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 à la suite dans Excel

Lors du volet précédent, nous avons appris à exporter des données de tables Access dans un nouveau classeur Excel que nous avons enregistré sur le disque. Pour cela, nous avons piloté une instance d'Excel par le code VBA Access. Dans ce nouveau volet, il est question de consolider les informations en exportant de nouvelles données, non plus dans un nouveau classeur, mais dans le même et à la suite des enregistrements précédemment importés. Cela signifie que par le biais de cette instance Excel, nous allons devoir être en mesure d'analyser les cellules existantes pour trouver la première case vide, afin de reprendre l'écriture dans l'enchaînement des précédentes données.

Base de données Access à télécharger
Pour développer cette solution, il est opportun d'appuyer les travaux sur le développement précédent ayant permis d'exporter les données Access dans Excel.

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

Comme vous le constatez, la décompression livre le fichier de la base de données, accompagné d'un sous-dossier nommé export. Il est vide pour l'instant. C'est lui qui doit accueillir le fichier Excel de la consolidation des informations Access.

• Double cliquer sur le fichier de la base de données pour l'ouvrir dans Access,
• Cliquer 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,

Nous retrouvons le formulaire que nous connaissons déjà bien. Une liste déroulante permet de choisir parmi l'une des tables de la base de données pour procéder à son exportation au clic sur le bouton Exporter. Mais la zone qui nous intéresse particulièrement ici, est celle située sous la liste déroulante. Elle concerne le mode d'exportation. Son nom d'objet à piloter en VBA est mode. S'il s'agit d'une première exportation, c'est la seconde case qui doit être cochée. S'il s'agit de réaliser une consolidation à la suite dans Excel, c'est la première case qui doit être cochée.

Pour résumer...
Avant de procéder à l'ajustement du code VBA, nous proposons de le survoler rapidement puisqu'il a fait l'objet des travaux précédents. Il est simplement question de nous rafraîchir la mémoire sur son mécanisme et sur son fonctionnement et de l'adapter au coup par coup.

• 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,
• Puis, cliquer sur le petit bouton associé à son événement Au Clic,

Nous basculons ainsi dans l'éditeur VBA Access entre les bornes de la procédure événementielle exporter_Click. Son code est déjà riche. C'est lui qui procède à l'exportation des données d'une table dans un nouveau classeur Excel.

Une méthode en particulier nécessite que nous ajoutions la référence à Excel au projet, malgré l'instanciation de sa classe déjà en vigueur. Cette méthode est celle qui permet de recueillir l'indice de ligne de la dernière cellule utilisée pour connaître celui à partir duquel il s'agit de poursuivre la consolidation des données.

• En haut de l'éditeur VBA Access, cliquer sur le menu Outils,
• Dans les propositions, choisir l'option Références,
• Dans la boîte de dialogue, cocher la case Microsoft Excel 16.0 Object Library,

Une fois encore, ce numéro dépend de la version d'Office installée sur votre machine.

• Valider ce choix en cliquant sur le bouton Ok de la boîte de dialogue,

Ce sont désormais tous les objets Excel avec leurs propriétés et méthodes qui se rendent disponibles dans ce développement VBA Access.

Dans ce code VBA, nous récupérons tout d'abord le nom de la table en variable et construisons le chemin d'accès au fichier Excel dans le sous dossier export :

...
nomTable = listeTables.Value
nomF = CurrentProject.Path & "\export\donnees.xlsx"
...

C'est maintenant la variable nomF qui sera utilisée en paramètre pour la sauvegarde dans le cas d'une première exportation et non d'une consolidation.

Ensuite, nous initialisons les variables objets pour piloter la base de données en cours, ses tables et leurs enregistrements mais aussi pour prendre possession d'Excel par le biais d'une instance :

...
Set base = CurrentDb()
Set enr = base.OpenRecordset(nomTable)
Set table = base.TableDefs(nomTable)
Set instanceE = CreateObject("Excel.Application")
...

Ouvrir le classeur Excel existant
Dès lors, le code VBA actuel se charge de créer un nouveau classeur (instanceE.Workbooks.Add) en arrière-plan (instanceE.Visible = False), pour accueillir la première exportation. C'est précisément là que doit intervenir la première adaptation du code VBA. Lorsqu'il s'agit d'une consolidation et non d'une première exportation, donc lorsque la première des deux cases est cochée, ce n'est pas un nouveau classeur qui doit être créé. C'est celui qui existe qui doit être ouvert pour être enrichi.

• Remplacer la ligne : instanceE.Workbooks.Add, par l'instruction conditionnelle suivante :

...
If (mode.Value = 2) Then
instanceE.Workbooks.Add
Else
instanceE.Workbooks.Open nomF
End If
...

Si la seconde case et cochée (mode.Value = 2) et qu'il s'agit donc d'une première exportation, nous continuons de créer un nouveau classeur pour accueillir les données. Sinon, nous ouvrons le classeur déjà construit grâce à la méthode Open de la collection Workbooks de l'instance Excel. Et pour cela, nous lui passons le chemin d'accès reconstruit dans la variable nomF.

Ne pas doubler les entêtes de colonnes
Ensuite, grâce à la boucle For Each qui suit, le code VBA inscrit les noms des champs de la table désignée en première ligne de la feuille Excel pour créer les entêtes de colonnes. Dans le cas d'une consolidation, ces entêtes ne doivent pas être répétés. C'est donc ici qu'intervient la deuxième adaptation du code VBA.

• Dans la boucle For Each, réaliser l'adaptation suivante :

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

C'est seulement dans le cas d'une première exportation, donc quand la seconde case est cochée (mode.Value = 2), que nous procédons à l'inscription des noms de champs en entêtes de la feuille Excel.

Première cellule non vide
Ensuite, l'actuel code VBA se contente de recaler la variable de ligne sur l'indice 2 pour engager l'écriture des enregistrements à la suite des entêtes. Mais dans le cas d'une consolidation, donc lorsque la première case est cochée, cette écriture doit se réaliser à la suite, soit à partir de la première cellule vide dans la première colonne de la feuille Excel. Et pour cela, le VBA Excel que nous pilotons grâce à cette instance, offre une méthode tout à fait dédiée. Il s'agit de la méthode SpecialCells de la collection Cells des cellules. Avec le paramètre xlCellTypeLastCell, elle permet de détecter la dernière cellule occupée. Il ne nous restera plus qu'à pointer sur la suivante.

• Avant la boucle Do parcourant les enregistrements, adapter le code VBA comme suit :

...
Next

If (mode.Value = 2) Then
compteL = 2
Else
compteL = instanceE.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1
End If
enr.MoveFirst
Do
...

Dans le cas d'une première exportation (mode.Value= 2), nous continuons de pointer sur la deuxième ligne de la feuille du classeur Excel. Dans le cas contraire, nous nous positionnons sur la première cellule vide placée après la précédente exportation (+1).

Sauvegarder les exportations
Ensuite, c'est la boucle Do qui se charge de parcourir tous les champs de chaque enregistrement pour extraire tout le détail dans chaque cellule de la feuille Excel.

...
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
...

Après cette boucle, survient la sauvegarde. Dans le cas d'une première exportation, le classeur doit être nommé pour être enregistré. Dans le cas d'une consolidation, puisque le classeur existe déjà, il doit simplement être enregistré.

• Remplacer la ligne de sauvegarde par l'instruction conditionnelle suivante :

...
Loop Until enr.EOF

If (mode.Value = 2) Then
instanceE.ActiveWorkbook.SaveAs nomF
Else
instanceE.ActiveWorkbook.Save
End If
instanceE.Quit
...

Lorsqu'il s'agit d'une consolidation, la méthode Save de l'objet VBA Excel ActiveWorkbook suffit à sécuriser les données sur le disque.

Exporter et consolider les données
Il est temps de tester ces ajustements pour constater non seulement que les exportations des données Access vers Excel fonctionnent mais aussi que les consolidations se produisent selon nos souhaits.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire Access (ALT + Tab),
• L'exécuter par exemple avec la touche F5 du clavier,
• Avec la liste déroulante, choisir la première table (societes1),
• Puis, cliquer sur le bouton Exporter,

A l'issue du traitement, vous voyez apparaître la boîte de dialogue de confirmation sur le bon déroulement de l'exportation des premières données de table. Et à ce titre, si vous ouvrez le sous dossier export dans l'explorateur Windows, vous notez la présence du nouveau fichier Excel donnees.xlsx, qui est d'ailleurs chargé en Kilo-Octets.

• Revenir sur le formulaire Access et choisir la table societes2 avec la liste déroulante,
• Cocher la première case (A la suite), puis cliquer sur le bouton Exporter,

A l'issue du traitement, la même boîte de dialogue intervient. A validation, si vous retournez dans le sous dossier export, vous constatez que le fichier donnees.xlsx s'est alourdi en raison vraisemblablement de la consolidation opérée.

Si vous procédez de la même façon avec la table societes3, la sentence est la même. Le fichier s'alourdit. Et si vous ouvrez le fichier Excel ainsi construit et que vous parcourez les enregistrements, vous constatez avec plaisir que toutes les données des tables différentes sont réunies les unes en-dessous des autres.



Les sauts dans l'énumération des clés primaires sont volontaires. Ces ruptures ont été créées pour bien remarquer le passage de l'une à l'autre table par le biais de ces exportations automatisées vers Excel organisées par le code VBA Access. Pour finir, sachez qu'il aurait été opportun de tester l'existence du fichier Excel avant de tenter la consolidation, en cas d'erreur de choix de la part de l'utilisateur. C'est une solution que nous avons apportée au cours d'une astuce précédente, facile à intégrer donc.