VBA: Ajouter des données dans Access à partir d’Excel 1



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!

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.

Ajouter des données Excel dans Access

Code VBA à utiliser pour exporter des données vers Access

Le code est assez simple et également assez "lisible". Il suffit de:

  1. déterminer le fichier de la base de données Access
  2. déterminer dans quelle table vous voulez ajouter des données
  3. ensuite le code crée une nouvelle entrée et y insère les valeurs de votre choix
  4. 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:
    Excel vers Access: Référence pour accdb        Excel vers Access: Référence pour mdb

Et voici donc le code VBA…

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_2018.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: Excel vers 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.

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_2018.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()
    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…

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()
    TransferExcelVersAccess = ExportGenerique("C:\Entreprise\Ventes\Ventes_2018.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é!

 

2 0

 




Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée.

Commentaire sur “VBA: Ajouter des données dans Access à partir d’Excel