Exportation de données depuis Excel vers une base de données Access, Excel VBA

Exportation de données depuis Excel vers une base de données Access, Excel VBA

Voici un guide détaillé sur l’exportation de données depuis Excel vers une base de données Access en utilisant VBA, avec des explications en français.
Étape 1 : Préparer la Base de Données Access
Avant d’exporter des données depuis Excel vers Access avec VBA, vous devez d’abord préparer votre base de données Access. Voici comment procéder :
1. Créer une base de données Access :

• Ouvrez Microsoft Access.
• Créez une nouvelle base de données (vous pouvez choisir une base de données vide).
• Enregistrez la base de données dans un répertoire facilement accessible (par exemple, C:\Users\VotreNom\Documents\ExportDB.accdb).

2. Créer une table dans Access :

• • Dans la base de données Access, créez une table où vous souhaitez exporter vos données.
  • Par exemple, supposons que vous exportez une liste d’employés.
  • Créez une table appelée Employees avec les champs suivants :
    • EmployeeID (NuméroAuto, Clé primaire)
    • FirstName (Texte)
    • LastName (Texte)
    • Department (Texte)
    • HireDate (Date/Heure)

Exemple :
Table Employees
—————————————–
| EmployeeID | FirstName | LastName | Department | HireDate |
—————————————————————
| NuméroAuto | Texte | Texte | Texte | Date |
Assurez-vous que les noms des champs correspondent à ceux que vous utiliserez dans vos données Excel.
3. Enregistrer et fermer Access :

• Enregistrez la base de données Access et fermez Access pour l’instant, car vous allez l’interagir avec depuis Excel via VBA.

Étape 2 : Code VBA Excel pour Exporter les Données vers Access
Maintenant, nous allons écrire le code VBA dans Excel pour exporter les données vers la base de données Access.
Code VBA pour Exporter des Données depuis Excel vers Access :

Sub ExportToAccess()
    ' Déclaration des variables
    Dim cn As Object
    Dim rs As Object
    Dim strDatabasePath As String
    Dim strSQL As String
    Dim row As Long
    Dim lastRow As Long
    Dim ExcelSheet As Worksheet
    ' Définir le chemin vers la base de données Access
    strDatabasePath = "C:\Users\VotreNom\Documents\ExportDB.accdb" ' Chemin vers votre base de données Access
    ' Définir la feuille de calcul contenant les données à exporter
    Set ExcelSheet = ThisWorkbook.Sheets("Sheet1") ' Ajustez selon le nom de votre feuille
    ' Créer une connexion vers la base de données Access
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDatabasePath & ";Persist Security Info=False;"
    cn.Open
    ' Trouver la dernière ligne de données dans Excel
    lastRow = ExcelSheet.Cells(ExcelSheet.Rows.Count, "A").End(xlUp).Row
    ' Boucler à travers chaque ligne de données (à partir de la ligne 2, supposant que la ligne 1 contient les en-têtes)
    For row = 2 To lastRow
        ' Construire la requête SQL pour insérer les données dans la table Employees
        strSQL = "INSERT INTO Employees (FirstName, LastName, Department, HireDate) " & _
                 "VALUES ('" & ExcelSheet.Cells(row, 1).Value & "', " & _  ' FirstName
                 "'" & ExcelSheet.Cells(row, 2).Value & "', " & _  ' LastName
                 "'" & ExcelSheet.Cells(row, 3).Value & "', " & _  ' Department
                 "#" & Format(ExcelSheet.Cells(row, 4).Value, "mm/dd/yyyy") & "#)" ' HireDate (format de date correct)
        ' Exécuter la requête SQL pour insérer les données dans la table Access
        cn.Execute strSQL
    Next row
    ' Libération des objets
    cn.Close
    Set cn = Nothing
    MsgBox "Exportation des données vers Access terminée avec succès !"
End Sub

Explication du Code :
1. Déclaration des Variables :

• cn : Il s’agit d’un objet de connexion ADODB qui permet d’interagir avec la base de données Access.
• rs : Un objet de recordset, qui n’est pas utilisé directement dans ce cas, mais peut être utile pour récupérer des données.
• strDatabasePath : Le chemin d’accès à votre fichier de base de données Access (à ajuster selon votre emplacement de fichier).
• strSQL : La chaîne de requête SQL utilisée pour insérer des données dans la base de données Access.
• row : Une variable utilisée dans la boucle pour itérer à travers les lignes dans la feuille Excel.
• lastRow : La dernière ligne de données dans la feuille Excel (utilisée pour déterminer jusqu’où boucler dans les données).
• ExcelSheet : La feuille de calcul qui contient les données à exporter.

2. Connexion à la Base de Données Access :

• • L’objet cn est initialisé pour se connecter à la base de données Access à l’aide d’une chaîne de connexion.
    • Provider=Microsoft.ACE.OLEDB.12.0 spécifie le fournisseur pour Access.
    • Le Data Source est le chemin d’accès à la base de données Access que vous avez créée précédemment.
    • Persist Security Info=False est inclus pour ne pas sauvegarder d’informations sensibles de sécurité dans la chaîne de connexion.

3. Boucle à Travers les Données Excel :

• La variable lastRow détermine la dernière ligne contenant des données dans la feuille Excel (en supposant que les données commencent à partir de la ligne 2 et que la ligne 1 contient les en-têtes).
• La boucle For commence à la ligne 2 et continue jusqu’à la dernière ligne de données, en construisant une requête SQL INSERT INTO pour chaque ligne de données.

4. Requête SQL d’Insertion :

• La chaîne strSQL insère les données depuis Excel dans la table Employees d’Access.
• Chaque valeur de colonne dans Excel est extraite à l’aide de ExcelSheet.Cells(row, column) où column représente le numéro de la colonne (1 pour FirstName, 2 pour LastName, etc.).
• La fonction Format est utilisée pour s’assurer que la date est dans le format correct (mm/dd/yyyy), qui est requis par Access pour les champs de type date.

5. Exécution de la Requête SQL :

• La méthode cn.Execute exécute la requête SQL et insère la ligne dans la table Access.

6. Nettoyage et Fin :

• Une fois la boucle terminée, la connexion est fermée (cn.Close) et l’objet de connexion est mis à Nothing pour libérer les ressources.
• Un message de confirmation est affiché pour indiquer que l’exportation a été réussie.

Résultat :
Après avoir exécuté le code VBA, les données de votre feuille Excel seront insérées dans la base de données Access. Chaque ligne d’Excel sera exportée comme un nouvel enregistrement dans la table Employees de la base de données Access. Si l’exportation réussit, vous verrez le message suivant :
Exportation des données vers Access terminée avec succès !
Conseils de Dépannage :

• Assurez-vous que les noms des colonnes dans Excel correspondent exactement à ceux de la table Access.
• Vérifiez le chemin d’accès à la base de données pour vous assurer qu’il est correct.
• Si vous rencontrez des erreurs, activez la bibliothèque Microsoft ActiveX Data Objects (ADO) dans vos références :
• Dans l’éditeur VBA, allez dans Outils → Références, puis cochez Microsoft ActiveX Data Objects 6.1 Library (ou une version similaire).

Ce guide vous fournit l’ensemble du flux de travail, de la préparation de la base de données Access à l’écriture et à l’exécution du code VBA pour l’exportation des données.