Cette fois-ci, je vous propose un sujet intéressant: l'interaction entre MS Excel et MS Access, et particulièrement la manière dont vous pouvez "envoyer" les information de l'Excel vers une base de données Access grâce à VBA.
Non seulement que c'est possible, mais c'est même assez simple et (surtout) rapide!
Sommaire
Access et Excel: faits l'un pour l'autre
Imaginez que vous avez une base de données Access contenant l'historique des ventes de votre entreprise. Et un de vos vendeurs vous envoie un fichier Excel avec ses chiffres. Si le format des deux tables est le même, l'import dans Access ne va pas poser de problème. Mais si la structure de la table Excel est différente que celle d'Access?
Il suffit de quelques lignes de VBA et l'import peut se faire facilement. L'avantage de cette approche est que vous pouvez choisir exactement quelle info contenu dans Excel vous voulez exporter vers Access.
Code VBA à utiliser pour exporter des données vers Access
Le code est assez simple et également assez "lisible". Il suffit de:
- déterminer le fichier de la base de données Access
- déterminer dans quelle table vous voulez ajouter des données
- ensuite le code crée une nouvelle entrée et y insère les valeurs de votre choix
- une fois les données transférés, il coupe la connexion à la base de données
Quelques remarques préliminaires:
- vous n'êtes pas obligés pas remplir toutes les colonnes de la base Access. Vous remplissez seulement celles que vous voulez.
- attention, les données transférées de l'Excel doivent être du même type que les colonnes du tableau Access qui doivent les accueillir. Ainsi, si vous envoyez du texte vers une colonne qui est définie dans Access comme Date, le code va bloquer…
- pour que le code fonctionne dans Excel, vous devez cocher la Référence "Microsoft Office XX.X Access database engine Object Library". Ceci est valable pour le travail avec les fichiers ".accdb". Pour les fichiers plus anciens (".mdb") vous devrez cocher la Référence "Microsoft DAO X.X Object Library" (les "X" correspondent aux numéros de version). Attention – vous ne pouvez pas utiliser les deux en même temps. Voir les captures d'écran qui suivent:
Et voici donc le code VBA…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub ExporterVersAccess() 'par Excel-Malin.com ( https://excel-malin.com ) ' >>> nécessite la Référence "Microsoft Office XX.X Access database engine Object Library" <<< Dim MonFichierAccess As Database Dim MaTableDansAccess As DAO.Recordset Set MonFichierAccess = OpenDatabase("C:\Entreprise\Ventes\Ventes_2021.accdb") Set MaTableDansAccess = MonFichierAccess.OpenRecordset("Ventes", dbOpenTable) MaTableDansAccess.AddNew MaTableDansAccess.Fields("Produit") = "Poires" ' ou par ex.: Range("B2").Value MaTableDansAccess.Fields("Quantite") = 18 ' ou par ex.: Range("C2").Value MaTableDansAccess.Fields("Prix_total") = 900 ' ou par ex.: Range("E2").Value MaTableDansAccess.Update MaTableDansAccess.Close MonFichierAccess.Close End Sub |
Comme vous voyez, vous pouvez choisir la manière dont vous sélectionnez les valeurs à exporter: cela peut être une valeur particulière, le contenu d'une cellule ou encore le résultat d'un calcul. La flexibilité est très grande.
Pour rendre le code VBA encore plus flexible, vous pouvez en faire une fonction personnalisée où les valeurs seront transmises en tant qu'arguments.
Fonction VBA générique: MS Excel vers MS Access
On peut faire plusieurs fonctions selon la situation. Voici quelques exemples…
Fonction VBA où le fichier Access est le même (même fichier, même table et même structure de la table)
Les informations sur le fichier Access sont dans la fonction, la seule chose qui manque sont les valeurs à exporter. Elles seront alors injectées comme arguments.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Public Function NouvelleVente(Produit As String, Quantite As Single, PrixTotal As Single) 'par Excel-Malin.com ( https://excel-malin.com ) ' >>> nécessite la Référence "Microsoft Office XX.X Access database engine Object Library" <<< Dim MonFichierAccess As Database Dim MaTableDansAccess As DAO.Recordset Set MonFichierAccess = OpenDatabase("C:\Entreprise\Ventes\Ventes_2021.accdb") Set MaTableDansAccess = MonFichierAccess.OpenRecordset("Ventes", dbOpenTable) MaTableDansAccess.AddNew MaTableDansAccess.Fields("Produit") = Produit MaTableDansAccess.Fields("Quantite") = Quantite MaTableDansAccess.Fields("Prix_total") = PrixTotal MaTableDansAccess.Update MaTableDansAccess.Close MonFichierAccess.Close End Function '-------------------------- ' exemple d'utilisation Sub Test1() Dim TransferExcelVersAccess TransferExcelVersAccess = NouvelleVente("voiture", 2, 15000) End Sub |
Et on peut aller encore plus loin…
Fonction VBA: Excel vers Access plus flexible
Dans cette fonction, on peut utiliser le nom du fichier et le nom de la table comme arguments variables. De plus, on peut également rendre variable les noms de colonnes et le s valeurs. La seule chose qui reste "fixe" alors est le nombre de champs que l'on veut remplir.
Prenons un exemple avec 3 valeurs qui doivent être exportées. On indique alors, comme arguments: chemin complet du fichier, le nom de la table, et les trois paires nom de colonne/valeur à transférer…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Public Function ExportGenerique(Fichier As String, NomDeTable As String, Colonne_1 As String, Valeur_1 As Variant, Colonne_2 As String, Valeur_2 As Variant, Colonne_3 As String, Valeur_3 As Variant) 'par Excel-Malin.com ( https://excel-malin.com ) ' >>> nécessite la Référence "Microsoft Office XX.X Access database engine Object Library" <<< Dim MonFichierAccess As Database Dim MaTableDansAccess As DAO.Recordset Set MonFichierAccess = OpenDatabase(Fichier) Set MaTableDansAccess = MonFichierAccess.OpenRecordset(NomDeTable, dbOpenTable) MaTableDansAccess.AddNew MaTableDansAccess.Fields(Colonne_1) = Valeur_1 MaTableDansAccess.Fields(Colonne_2) = Valeur_2 MaTableDansAccess.Fields(Colonne_3) = Valeur_3 MaTableDansAccess.Update MaTableDansAccess.Close MonFichierAccess.Close End Function '-------------------------- ' exemple d'utilisation Sub Test2() Dim TransferExcelVersAccess TransferExcelVersAccess = ExportGenerique("C:\Entreprise\Ventes\Ventes_2021.accdb", "Ventes", "Produit", "Fraise", "Quantite", 50, "Prix_total", 5000) End Sub |
Notez que pour rendre cette fonction générique au maximum, on utilise pour les valeurs le type "Variant" – ainsi, peu importe le type défini pour chaque colonne en Access, cette fonction marchera dans n'importe quelle situation…
Et comme vous venez de le voir, gérer les données dans Access à partir d'Excel n'est pas bien compliqué!
Pour aller plus loin en VBA…
Pour terminer ce tutoriel, je vous propose d'autres articles sur VBA qui pourraient vous être utiles:
- Liste de toutes les fonctions VBA
- Débloquer VBA dans les fichiers téléchargés sur internet
- Gestion des fichiers en VBA
- Dates et heures en VBA – comment ça marche?
- Comment utiliser RECHERCHEV et d'autres fonctions Excel directement en VBA
- Envoyer un email automatiquement avec VBA
15 commentaires sur “VBA: Ajouter des données dans Access à partir d’Excel”
Super! Merci pour ce tutoriel.
Les explications aident beaucoup à la compréhension.
Je pense qu'il y a une légère erreur pour chaque ligne n°8 :
La bonne syntaxe est :
Set MonFichierAccess = DBEngine.OpenDatabase(Fichier)
Bonjour Harison,
merci pour votre remarque.
Mon code tel qu'il est ne fonctionne pas pour vous?
Je viens de tester les deux (le code actuel et le code avec votre modification) et ils fonctionnent tous les deux de la même manière.
Ou bien votre remarque concerne purement la syntaxe?
Bien à vous, Martin
utile pour débuter.
il y a une produit qui le fait automatiquement………
*******************************************************
**** Contenu modifié par l'administrateur ****
**** Raison: SPAM ****
*******************************************************
Bonjour,
la prochaine fois, ce serait sympa de me consulter avant de faire la promotion des logiciels avec des liens de téléchargement etc.
Vous croyez vraiment que je vais laisser ici un lien pointant vers un fichier ".EXE" potentiellement infecté par des virus???
Martin HUDEC – auteur de Excel-Malin.com
Excellent tutoriel félicitation !
Bonjour,
Merci pour le tuto. Existe-t-il une fonction semblable à AddNew qui, au lieu d'ajouter une ligne dans Access, permet d'accéder à une valeur pour la modifier ?
Merci d'avance,
Hugo
Bonjour Hugo,
oui, c'est possible.
Vous pouvez voir les explication et l'exemple d'un code complet ici:
https://software-solutions-online.com/updating-tables-access-database-using-excel-vba/
J'espère que cela va vous aider. Cordialement, Martin
Merci pour votre réponse, je regarde cela
Autre question, comment faire fonctionner ces opérations dans une table non locale, connectée en ligne à une liste SharePoint ?
Merci d'avance,
Hugo
Je demande cela car j'ai une erreur à la ligne :
Set MaTableDansAccess = MonFichierAccess.OpenRecordset("PAD test v7", dbOpenTable)
lorsque j'exécute le code. (PAD test v7 étant ma liste connectée à SharePoint)
L'erreur affiche simplement "Erreur d'exécution 3219: Opération non valide"
Bonjour,
J'aimerais savoir: J'ai une bdd acces qui contient une table que je souhaite mettre à jour régulièrement en y ajoutant des données mensuelles. Les données arrivent sous Excel et actuellement je les rentre manuellement. Peut on utiliser ce code en y ajoutant une boucle qui prendra à chaque exécution les différentes lignes Excel et les rajouter dans Access ?
Bonjour,
j'ai réussi a faire fonctionner votre bout de code lié au sub ExporterVersAccess en l'applicant dans un userform
par contre, j'ai voulu utiliser la 1ere fonction proposé et ca plante a lequivalent du sub test1 TransferExcelVersAccess
Ca me demande de déclarer cette variable. dans vos explications, je ne vois pas comment vous l'avez declaré.
Pouvez vous m'aider ?
Je vous remercie pour votre aide
Bonjour Fabrice,
merci pour cette remarque. En effet, une petite omission de ma part…
J'ai ajouté les définitions dans les codes.
Cordialement, Martin
Bonjour,
merci pour la méthode , mais comment supprimer une ligne précédemment ajouter de excel vers acces
slts,