Importer des données d'une feuille Excel en VBA Access

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


Sujets que vous pourriez aussi aimer :


Importer depuis Excel

A l'occasion de précédentes astuces, nous avions appris à exporter des données dans une feuille d'un classeur Excel, mais aussi à les consolider en exportant les informations à la suite des précédentes. Ici, nous proposons de réaliser le cheminement inverse. Il est donc question d'importer les données d'un tableau Excel dans une table Access, par le code VBA.

Base de données Access à télécharger
Pour développer cette nouvelle solution, nous suggérons d'appuyer l'étude sur une base de données Access offrant déjà un formulaire ainsi que sur un classeur Excel hébergeant des données à réceptionner.

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

Comme vous le voyez, la décompression livre le fichier de la base de données. Il est nommé importer-depuis-excel.accdb et il est accompagné d'un sous dossier intitulé source.

• Double cliquer sur ce sous dossier pour l'ouvrir,

Vous y découvrez la présence d'un fichier Excel. Il est nommé tableau-excel.xlsx.

• Double cliquer sur ce dernier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Vous découvrez la base de données Excel dont il s'agit d'importer le contenu structuré dans Access. Ce sont les colonnes B, C, D et E qui nous intéressent sur le nom de l'activité, l'activité précisément, le département et la ville. Il s'agit donc des rangées 2, 3, 4 et 5.

Si vous réalisez le raccourci clavier CTRL + Fin, vous atteignez la fin du fichier, sur la ligne 1244. Vous constatez que le nom de l'activité est Restaurant Thi-Mi. Nous allons devoir bâtir un code VBA Access adaptatif capable de rapatrier toutes ces données, jusqu'à la dernière, dans la table Access.

• Fermer le classeur Excel en cliquant sur la croix de sa fenêtre,
• Revenir à la racine du dossier de décompression,
• Puis, double cliquer sur le fichier importer-depuis-excel.accdb pour l'ouvrir dans Access,
• Cliquer sur le bouton Activer le contenu du bandeau de sécurité,
• Dans le volet de navigation sur la gauche, double cliquer sur le formulaire fImport,

Ainsi, nous l'exécutons.



Ce formulaire est composé d'un contrôle activeX progressBar pour rendre compte de l'état d'avancement du processus d'importation par le code VBA que nous allons construire. Il est aussi doté de deux boutons. Le premier est celui qui nous intéresse. Il est intitulé Importer. C'est aussi son nom d'objet. C'est lui qui doit lancer le traitement VBA consistant à interroger toutes les cellules du classeur Excel mentionné par l'utilisateur, pour rapatrier toutes les valeurs dans la table Access. Cette table est nommée societes.

• Double cliquer sur le nom de cette table depuis le volet de navigation,

Ainsi, nous l'affichons en mode feuille de données. Elle est vide bien entendu. En consultant les noms de ses champs, vous constatez qu'elle attend de réceptionner les informations du tableau Excel.



C'est le code VBA que nous devons déclencher au clic sur le bouton Importer qui doit s'en charger.

Les librairies externes
Nous devons commencer par ajouter deux références au projet. La première doit permettre de piloter les boîtes de dialogue standard d'Office. La seconde doit fort naturellement permettre de manipuler une instance d'Excel.

• Fermer la table puis à 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 Importer pour le sélectionner,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton associé à son événement Au clic,
• Dans la boîte de dialogue, choisir le Générateur de code et valider par Ok,

Nous basculons ainsi dans l'éditeur VBA Access, entre les bornes de la procédure événementielle importer_Click.

• En haut de l'éditeur, déployer le menu Outils,
• Dans les propositions, choisir l'option Références,

Les deux librairies nécessaires sont déjà cochées. Nous avions anticipé. Elles se nomment respectivement Microsoft Excel 16.0 Object Library et Microsoft Office 16.0 Object Library. Mais si elles n'avaient pas été présentes, il aurait fallu les ajouter pour les besoins de ce développement spécifique.

• Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur la feuille de code.

La déclaration des variables
Ensuite, un certain nombre de variables sont nécessaires. Il s'agit notamment de piloter l'instance d'Excel, la boîte de dialogue d'ouverture de fichiers en encore la base de données en cours.

• Dans les bornes de la procédure, ajouter les déclarations et affectations suivantes :

...
Dim boite As FileDialog: Dim chemin As String
Dim instanceE As Excel.Application
Dim base As Database: Dim requete As String
Dim ligne As Long: Dim nbLignes As Long
Dim nom As String: Dim act As String: Dim dep As String: Dim ville As String

Set boite = Application.FileDialog(msoFileDialogFilePicker)
If boite.Show Then chemin = boite.SelectedItems(1)
...

Nous déclarons l'objet boite comme un FileDialog grâce à la deuxième des deux références ajoutées au projet. C'est lui qui permettra de piloter cette boîte de dialogue d'ouverture. Dans la variable chemin, nous stockerons le chemin d'accès au classeur Excel désigné par l'utilisateur, par le biais de cette boîte de dialogue. L'objet instanceE typé comme un Excel.Application permettra d'instancier la classe du tableau Office. La variable base est typée comme un objet Database pour pouvoir piloter la base de données en cours. Nous le verrons au moment de son initialisation. Sur cette dernière, la variable requete doit mémoriser la syntaxe de la requête insertion à y exécuter. Les deux variables suivantes sont déclarées comme des entiers longs (As Long). La première doit suivre les lignes parcourues dans le tableau de la feuille Excel. La deuxième (nbLignes) doit mémoriser le nombre total de lignes du tableau Excel. Nous l'utiliserons pour étalonner la barre de progression. Enfin, les quatre dernières variables sont typées comme des textes (As String). Enregistrement par enregistrement, elles doivent récolter les informations de champs depuis le tableau Excel, pour les importer dans la table Access.

Ensuite, nous initialisons (Set) notre objet boite sur une boîte de dialogue d'ouverture de fichiers (msoFileDialogFilePicker). Nous ouvrons cette boîte de dialogue (Show). Puis, nous récoltons le chemin d'accès au classeur désigné par l'utilisateur, dans la variable chemin. La collection SelectedItems représente tous les fichiers sélectionnés. Mais comme cette boîte non configurée n'accepte pas la multi sélection, nous savons que le fichier désigné se trouve en première position (1) de la collection.

Vérifier le fichier désigné
La suite du traitement ne doit être entreprise que dans la mesure où nous avons la certitude que l'utilisateur a bien désigné un fichier et donc qu'il n'a pas cliqué sur le bouton Annuler de la boîte de dialogue. D'ailleurs et à ce sujet, nous aurions pu émettre un filtre sur cette boîte pour qu'elle n'autorise que les fichiers Excel. Mais ça, nous savons le faire et ce n'est pas l'optique direct de cette formation.

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

...
If chemin <> "" Then
Set instanceE = CreateObject("Excel.Application")
instanceE.Visible = False
instanceE.Workbooks.Open chemin

End If
...

Nous créons une instruction conditionnelle destinée à vérifier que le chemin d'accès n'est pas vide (<>""). Si le critère est honoré, nous entreprenons l'instanciation de la classe Excel, grâce à la fonction VBA CreateObject. Dès lors, nous exploitons les méthodes et propriétés héritées pour ouvrir ce fichier (Open) désigné (chemin), en arrière-plan (Visible = False).

Parcourir les lignes de la feuille Excel
Maintenant que l'instance Excel est créée, nous pouvons accéder au contenu du fichier désigné, par le code VBA Access. Sur ce contenu, nous allons engager une boucle destinée à parcourir toutes les lignes du tableau, tant que la première cellule vide n'est pas détectée, donc tant que la fin des données n'est pas atteinte.

• A la suite du code de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
nbLignes = instanceE.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
Set base = CurrentDb()
ligne = 2

While instanceE.Sheets(1).Cells(ligne, 2).Value <> ""

Wend
...

Tout d'abord, nous travaillons sur l'instance Excel. Nous exploitons la méthode SpecialCells de la collection Cells pour connaître la ligne (Row) de la dernière cellule non vide de la feuille (xlCellTypeLastCell). Nous stockons cette information dans la variable nbLignes. Nous l'avons dit, nous l'exploiterons pour étalonner la barre de progression sur le formulaire.

Ensuite, nous utilisons la fonction VBA Access CurrentDb pour initialiser (Set) notre objet base de données (base) pour qu'il hérite des propriétés et méthodes lui permettant de piloter la base de données en cours. Nous initialisons la variable ligne sur le premier indice (2) à partir duquel il s'agit de récupérer les données, depuis la feuille Excel. Et précisément, sur cette feuille, grâce à l'instance Excel, nous engageons une boucle pour parcourir toutes les lignes du tableau, tant que la première cellule vide n'est pas détectée (Cells(ligne, 2).Value<> "").

Récupérer les données d'Excel
A chaque passage dans cette boucle, nous devons récupérer les informations de champs, ligne à ligne. Pour pointer sur chaque colonne, nous devons exploiter l'objet VBA Excel Cells.

• Dans les bornes de la boucle, ajouter les instructions VBA suivantes :

...
With instanceE.Sheets(1)
nom = .Cells(ligne, 2).Value
act = .Cells(ligne, 3).Value
dep = .Cells(ligne, 4).Value
ville = .Cells(ligne, 5).Value

ligne = ligne + 1
End With
...

Sur la première feuille du classeur (Sheets(1)), nous récoltons les données de chaque champ (2, 3, 4 et 5) pour la ligne en cours (ligne) pour les stocker dans les variables respectives, prévues à cet effet. A chaque passage bien sûr, nous n'oublions pas d'incrémenter la variable ligne (ligne = ligne + 1), pour déplacer l'analyse sur la ligne suivante du tableau Excel.

Importer les données dans Access
Une fois ces données stockées, nous devons les exploiter pour les insérer dans la table Access. Pour cela, nous devons construire une requête d'ajout.

• A la suite, avant l'incrémentation de la variable ligne, ajouter les instructions VBA suivantes :

...
dep = .Cells(ligne, 4).Value
ville = .Cells(ligne, 5).Value

requete = "INSERT INTO societes(societes_nom, societes_activite, societes_departement, societes_ville) VALUES ('" & nom & "','" & act & "','" & dep & "','" & ville & "')"
base.Execute requete

evolution.Value = Int((ligne * 100) / nbLignes)

ligne = ligne + 1
End With
...

Il s'agit d'une syntaxe SQL classique permettant d'enrichir chaque champ de la table de destination , listés dans les premières parenthèses, avec les informations récoltées, concaténées et listées dans les secondes parenthèses. Après, c'est la méthode héritée Execute par l'objet base qui permet d'exécuter cette syntaxe. Enfin, nous calibrons la barre de progression (nommée evolution) pour qu'elle progresse en même temps que l'analyse des lignes du tableau Excel.

Décharger les objets
Pour finir proprement, nous devons décharger les objets de programmation et à avertir l'utilisateur que le traitement est terminé.

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

...
Wend
instanceE.Quit
Set instanceE = Nothing
base.Close
Set base = Nothing
End If

MsgBox "L'importation des données Excel est terminée"

Set boite = Nothing
End Sub
...

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• Exécuter ce formulaire en enfonçant par exemple la touche F5 du clavier,
• Cliquer sur le bouton Importer,
• Avec la boîte de dialogue, double cliquer sur le fichier Excel depuis le sous dossier source,

Comme vous pouvez le voir, le processus s'enclenche et la barre de progression rend compte de l'avancement. A l'issue, la boîte de dialogue programmée, informe l'utilisateur de la fin du traitement d'importation des données Excel.

Et si vous ouvrez la table societes qui était vide, vous en avez instantanément le coeur net. Toutes les informations externes ont été récupérées par le code VBA Access.