Echanges de données entre Excel et Access

Page mise à jour le : 1/08/2002

Vous trouverez dans cette rubrique des exemples de programmes permettant d'envoyer des données Excel vers Access, de récupérer des données d'Access dans Excel ou de mettre à jour des enregistrements dans Access depuis Excel.

Ces exemples fonctionnent avec Access et Excel 2000. Ils n'ont pas été testé sous la version 97.
Pour que ces exemples fonctionnent, vous devrez créer une référence de bibliothèques d'objets d'accès
aux données de Microsoft (DAO)
. Dans Excel, choisissez la commande Références du menu Outils de Visual Basic Editor et cochez Microsoft DAO 3.6 Object Library.

Un lien intéressant (mes sources) traitant de External Data Access with DAO (Data Access Objects) and ADO (ActiveX Data Objects)

Envoyer les données d'une feuille Excel vers une table Access.
Récupérer les données d'une table Access dans une feuille de calcul Excel.
Récupérer les données d'une table Access dans une feuille de calcul Excel via une requête .
Mettre à jour un enregistrement dans Access depuis Excel.


 
Envoyer les données d'une feuille Excel vers une table AccessRetour au début

 
Ce programme permet de copier les données d'une feuille de calcul Excel
vers une table Access.

Définition des éléments utilisés :
Nom de la base de données    -->    Commandes.mdb
Table recevant les données Excel    -->    Factures
Champs de la table Factures    -->    NoFacture, Client, Date et Solde
Feuille de calcul Excel d'où l'on exporte les données    -->    DAOSheet

Sub WritingWorksheetData_DAO()

     Dim Plage As Range

     Dim Array1 As Variant

     Dim x As Variant

     Dim Db1 As Database

     Dim Rs1 As Recordset

     ' Ouverture de la base de données Commandes.mdb

     Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path & "\Commandes.mdb" )

     ' Ouverture de la table Factures

     ' Un objet Recordset représente les enregistrements d'une table

     Set Rs1 = Db1.OpenRecordset( "Factures" , dbOpenDynaset)

     ' Détermination de la taille de la plage à envoyer vers Access

     Set Plage = Worksheets( "DAOSheet" ).Range( "A1" ).CurrentRegion.Offset( 1 , 0 )

     Set Plage = Plage.Resize(Plage.Rows.Count - 1 , Plage.Columns.Count)

     Plage.Select

     ' Lecture de la plage pour renvoyer une valeur contenant un tableau

     Array1 = Plage.Value

     ' Ecriture des données depuis Excel vers les enregistrement de la table Factures

     For x = 1 To UBound (Array1, 1 )

          With Rs1

               .AddNew

               .Fields( "NoFacture" ) = Array1(x, 1 )

               .Fields( "Client" ) = Array1(x, 2 )

               .Fields( "Date" ) = Array1(x, 3 )

               .Fields( "Solde" ) = Array1(x, 4 )

               .Update

          End With

     Next

     ' Fermeture de la base Commandes.mdb

     Db1.Close

     ' Effacement des données copiées vers la base (sauf les titres)

     With Selection.CurrentRegion

          Intersect(.Cells, .Offset( 1 )).Select

     End With

     Selection.ClearContents

End Sub


Les données Excel
 
Les données copiées dans la table Factures

Récupérer les données d'une table Access dans une feuille de calcul Excel Retour au début

 
Ce programme permet de récupérer les données d'une table Access
et de les copier dans une feuille Excel.

Définition des éléments utilisés :
Nom de la base de données    -->    Commandes.mdb
Table possédant les données à récupérer dans Excel    -->    Factures
Champs de la table Factures    -->    NoFacture, Client, Date et Solde
Feuille de calcul Excel recevant les données de la table;   -->    DonnéesDataBase

Sub CopyFromRecordset_DAO()

     Dim Db1 As Database

     Dim Rs1 As Recordset

     ' Ouverture de la base de données

     Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path & "\Commandes.mdb" )

     ' Ouverture de la table Factures

     ' Un objet Recordset représente les enregistrements d'une table

     Set Rs1 = Db1.OpenRecordset( Name := "Factures" , Type :=dbOpenDynaset)

     ' Effacement des données existantes dans la WorkSheet (sauf les titres)

     ' et copie des enregistrements

     With Worksheets( "DonnéesDataBase" ).Range( "A2" )

          With Selection.CurrentRegion

               Intersect(.Cells, .Offset( 1 )).Select

          End With

          Selection.ClearContents

          .CopyFromRecordset Rs1

     End With

     'Fermeture de la Base de données

     Db1.Close

End Sub


Les données de la table Factures
 
Les données récupérées dans la feuille Excel

Récupérer les données d'une table Access dans une feuille de calcul ExcelRetour au début
via une requête

 
Ce programme permet de récupérer les données d'une table Access
par l'intermédiare d'une requête et de les copier dans une feuille Excel.

Définition des éléments utilisés :
Nom de la base de données    -->    Commandes.mdb
Table possédant les données à récupérer dans Excel    -->    Factures
Champs de la table Factures    -->    NoFacture, Client, Date et Solde
Nom de la requête    -->    Factures pour un client
Code SQL    -->    SELECT Factures.Nofacture, Factures.Client, Factures.Date, Factures.Solde FROM Factures
                                    WHERE (((Factures.Client) Like "ALLEE DES VINS"));
Feuille de calcul Excel recevant les données de la table;   -->    DonnéesDataBase

Sub CopyFromRecordset_DAO()

     Dim Db1 As Database

     Dim Rs1 As Recordset

     ' Ouverture de la base de données

     Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path & "\Commandes.mdb" )

     ' Ouverture de la table Factures

     ' Un objet Recordset représente les enregistrements d'une table

     Set Rs1 = Db1.OpenRecordset( Name := "Factures pour un client" , Type :=dbOpenSnapshot)

     ' Effacement des données existantes dans la WorkSheet (sauf les titres)

     ' et copie des enregistrements

     With Worksheets( "DonnéesDataBase" ).Range( "A2" )

          With Selection.CurrentRegion

               Intersect(.Cells, .Offset( 1 )).Select

          End With

          Selection.ClearContents

          .CopyFromRecordset Rs1

     End With

     'Fermeture de la Base de données

     Db1.Close

End Sub


Les données de la table Factures
 
Les données récupérées dans la feuille Excel
 
La requête Factures pour un client en mode Création

Mettre à jour un enregistrement dans Access depuis ExcelRetour au début

 
Ce programme permet de mettre à jour dans la table Factures, le champ (Solde) correspondant
à l'enregistrement dont le N° de facture est égal à la variable N°Facture.

Définition des éléments utilisés :
Nom de la base de données    -->    Commandes.mdb
Table possédant les données à mettre à jour;   -->    Factures
Champ de la table Factures à mettre à jour    -->    Solde
Recherche à partir du    -->    NoFacture
Note : Afin de proposer les 2 possibiltés de syntaxe, l'exemple se propose de tester quel
est le type de la variable Nofacture car la syntaxe est différente suivant que la variable est
un nombre (vbInteger) ou une chaîne (vbString).

Sub UpdateDataAccess_DAO()

     Dim Db1 As Database

     TypeVariable = VarType (NumFacture)

     ' Ouverture de la base de données

     Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path & "\Commandes.mdb" )

     ' Mise à jour de l'enregistrement

     If TypeVariable = 2 Then        ' La variable Numfacture est de type vbInteger (2)

          Db1.Execute "UPDATE Factures SET Solde= 'Oui' WHERE NoFacture = " & NumFacture

     Else        ' La variable Numfacture est de type vbString (8)

          Db1.Execute "UPDATE Factures SET Solde = 'Oui' WHERE NoFacture = ' " & NumFacture & " ' "

     End If

     Db1.Close

End Sub