Excel et Access

Le 09/05/2019 à 11:47

Bonjour a tous,

Je dois, au boulot, créé une base Access via un fichier Excel. Que les données entrées sur excel soit stockées sur Access puis une partie chargé par Excel.

J'aimerai faire importer les données d'Access sur Excel sur une cellule précise sans prendre les en-têtes, le problème c'est comment ne pas prendre les en-têtes dans l'importation par requête ?

Dois-je faire une macro qui passe les valeur en plage puis supprime l'en-tête ?

je dois faire en sorte que les données soit importé à l'ouverture du fichier et exporter a la fermeture, tout cela est possible sans macro ? Si oui comment ?

Merci du temps que vous consacrerez pour moi.

Cordialement,

Arthur.

Le 09/05/2019 à 13:54

Bonjour,

C'est assez simple en fait.

Pour commencer, ci-joint un module avec 3 fonctions génériques pour ce connecter, lire, puis se déconnecter d'une base Access (on copie/colle le contenu du txt vers un nouveau module dans excel).

La fonction de lecture "Select_Db" demande une requête Sql en 1er argument, et 0 si on ne veut pas l'entête ou 1(optionnel) si on en veut.

Pour lire des données d'une base access on écrit alors un code du genre :

Sub Lire_Access()
Dim Req As String, T As Variant

    Connect_Access ThisWorkbook.Path & "\LaBaseAccess.accdb"    
    Req = "SELECT champs1,champs2 FROM [LaTableAccess]"
    T = Select_Db(Req, 0) ' ou Select_Db(Req, 1) si on veut l'entête
    ActiveSheet.Range("A1").Resize(UBound(T, 1), UBound(T, 2)) = T
    Close_Cnx
End Sub

Ici pour lister les 'champs1' et 'champs2' de la table 'LaTableAccess' de la base 'LaBaseAccess.accdb' et les coller dans la feuille courante à partir de A1

Sinon, pour créer une base c'est simple aussi :

* on crée le fichier :

Sub Creer_Base_Access()
Dim NDF As String, Provider As String

    NDF = ThisWorkbook.Path & "\LaBaseAccess.accdb"
    If Not ExisteFichier(NDF) Then
        Provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
        CreateObject("ADOX.Catalog").Create Provider & NDF
    End If
End Sub

* on crée une table :

Sub Creer_Table()
Dim NDF As String, LaTable As String

    NDF = ThisWorkbook.Path & "\LaBaseAccess.accdb"
    LaTable = "CREATE TABLE LaTableAccess (" & _
                "Id integer not null, " & _
                "Nom varchar(25) null, " & _
                "Prenom varchar(25) null, " & _
                "Adresse varchar(25) null, " & _
                "CP varchar(5) null, " & _
                "Ville varchar(30) null, " & _
                "Tel varchar(15) null, " & _
                "Date_anniv Date null)"

    Connect_Access NDF
    Cnx.Execute LaTable
    Close_Cnx
End Sub

* ensuite pour remplir, c'est soit avec des "Insert" ou des "Update" avec un simpe .execute :

    NDF = ThisWorkbook.Path & "\LaBaseAccess.accdb"
    Req = "INSERT INTO [LaTableAccess] ... blablabla" ou   = "UPDATE [LaTableAccess] SET ... blablabla"

    Connect_Access NDF
    Cnx.Execute Req
    Close_Cnx

Pierre

46module-access-9mai2019.txt (1.84 Ko)
Le 09/05/2019 à 14:30

Merci pierrep56 pour ta réponse,

avec tes fonction, est-il possible de "copier-coller" tout le tableau d'excel et le mettre dans la BdD Access en remplaçant les données ?

L'idée étant de faire une copie de sauvegarde des données dans Access, il faudrait recréé une table et faire une boucle sur les insert ligne par ligne, ou on peut tout exporter d'un coup avec une requête ?

Cordialement.

EDIT: Ou sinon comment détecter les lignes qui ont été modifiées afin de les mettres à jours dans la bdd ?

Au début j'ai pensé à "Supprimer les doublons" mais, et c'est normal, redécale les lignes afin qu'elles soient à la suite après la suppression des doublons. Je ne vois pas d'autre moyen pour savoir quelles lignes ont étés modifiées. Aurais-tu des idées ?

Le 09/05/2019 à 15:15

C'est curieux comme démarche!?

Pour ma part je préfère avoir des données sur des accdb et les lire au fil des besoins avec excel pour affichage, bilan, ou autre présentation de résultat. La saisie/modif se faisant à partir d'excel (via feuille ou userform). Et à la fermeture du xls, on le vide des données (qui restent stockées bien au chaud dans le accdb).

Ensuite, pour les sauvegardes des données, je préfère faire des copies des accdb sur des supports différents (réseau, disque dur externe, clé USB, ...). Mais ça me parait contre-productif d'avoir les mêmes données sur accdb et sur xls en même temps.

Sinon si on a une sélection de quelques données du accdb sur une feuille xl, on peut par exemple utiliser l'évènement Worksheet_Change pour détecter une modif sur une ligne et déclencher un Update. Ou un Worksheet_BeforeRightClick ou Worksheet_BeforeDoubleClick pour valider un ensemble de modif sur une ligne, toujours pour déclencher l'Update (via les fonctions génériques données plus haut). Sur un userform de saisie, un simple bouton de validation fait l'affaire.

Pierre

Le 09/05/2019 à 16:37

Merci pour ta réponse,

Actuellement dans l'entreprise nous avons un gros fichier excel qui ralentis de plus en plus. Le fichier est relié de part et d'autre à d'autres fichiers qui viennent prendre des valeurs ou en éditer.

D’où l'idée d'avoir ce fichier "vierge" à l'ouverture pour la rapidité et la je voulais voir si c'était plus "rentable" d'y importer les données et de les exporter à la sortie.

J'ai aussi trouvé un moyen pour ne garder que les élément uniques, ce qui me permettrait d'utiliser le update sur la ligne qu'il faut

Merci pour ton aide, je reviendrai vers toi si besoin

Cordialement.

Le 09/05/2019 à 18:19

bonjour

salut Pierre

comme toi je préconise la saisie dans Access (et non dans Excel, pourquoi rester dans Excel ? )

puis on ouvre Excel, menu Données, Obtenir, de base Access. Là on récupère les données (en lecture), et on en fait ce qu'on veut.

note : pour lire et faire des synthèses, analyses et graphiques, on fait mieux et le fichier est ultra-léger si on utilise Power BI (Desktop est gratuit)

amitiés

Le 09/05/2019 à 18:36
jmd a écrit :

pourquoi rester dans Excel ?

Salut jmd,

Pour moi, il y a une raison très simple : ma version d'Office est une "Famille et Etudiant", donc sans Access ...

Ce qui n'empêche aucunement de créer/lire/modifier des données dans des accdb ou mdb via ADO, car ADO c'est simple et le SQL est un langage très puissant et gratuit aussi.

On peut aussi modifier la structure de la base et des tables via ADO.

Pierre

PS simple question : peut-on créer un accdb depuis Excel avec ton Power-bi?

Le 10/05/2019 à 08:58

re

Pierre,

Power BI est un logiciel de BI (en français on dit "décisionnel")

Il ne sait qu'analyser des données et aussi les lire grâce au puissant ETL qu'est Power Query (disponible aussi dans Excel)

Il ne peut pas écrire de données (mais à petite dose oui, uniquement pour son usage pas pour usage par Excel par exemple)

Il ne peut pas créer de table Excel, ni Access ni txt (mais il peut exporter des tables avant ou après retraitement au format csv, ce qui est très pratique et permet de dialoguer avec Excel)

La version Desktop est gratuite, essaye-le. Attention, il est addictif

je comprends que travailler sur base Access sans Access soit parfois obligatoire

mais puis-je approuver ?

qu'est-ce qui garantit par exemple l'intégrité référentielle, à part le soin constant et habile du programmeur ?

amitiés

Le 10/05/2019 à 09:22
jmd a écrit :

qu'est-ce qui garantit par exemple l'intégrité référentielle

Salut jmd,

Ce n'est pas très dur de le prévoir dans le code, il suffit d'avoir la structure de la base en tête et de lancer un simple Delete (sql) sur les tables liées.

Bonne journée

Pierre

Le 10/05/2019 à 15:09

arf !

c'est pas fait pour les têtes en l'air comme moi

Le 15/05/2019 à 09:20

Bonjour, je continu dans ce sujet car ma question a un rapport avec les fonctions que pierrep56 m'a donnée.

Y a t-il une limite de caractère dans la requête que l'on peut envoyé par ses fonctions ?

Cordialement.

Le 15/05/2019 à 14:25

C'est bon, mon message d'erreur qui, je croyais, était du à une limite de caractères (car mon msgbox n'affichait pas toute ma requête) n'était du qu'au crochet que j'avait oubliés (car ma table comporte des nom de colonne avec espace, car elle est créée par exportation du tableau excel).

Merci de votre aide en tout cas,

cordialement,

Arthur.

Le 15/05/2019 à 14:25

Salut,

Si le texte de la requête est long, il est possible de la scinder en plusieurs parties.

Pour exemple, voici une requête "saucissonnée" issue d'une de mes appli :

     requete = "SELECT O1.PAYS, O1.FOURNISSEURS, O1.PRODUITS, O1.MOIS, O1.T14, O2.T15, O1.V14, O2.V15 " & _
                " FROM(SELECT  PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES, SUM(CA) AS T14, SUM(VOL) AS V14 " & _
                " FROM [" & Data1 & "] " & _
                " WHERE ANNEES=2014" & _
                " GROUP BY PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES) AS O1" & _
                " LEFT JOIN (SELECT  PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES, SUM(CA)AS T15, SUM(VOL)AS V15 " & _
                " FROM [" & Data1 & "] " & _
                " WHERE ANNEES=2015" & _
                " GROUP BY PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES) AS O2" & _
                " ON O1.PAYS=O2.PAYS AND O1.FOURNISSEURS=O2.FOURNISSEURS AND O1.PRODUITS=O2.PRODUITS"

    If Not Filtre1 = "" Then
        requete = requete & " WHERE O1.PAYS='" & UCase(Filtre1) & "' "
        If Not Filtre2 = "" Then requete = requete & " AND O1.FOURNISSEURS='" & UCase(Filtre2) & "' "
        If Not Filtre3 = "" Then requete = requete & " AND O1.PRODUITS='" & UCase(Filtre3) & "' "
    Else
       If Not Filtre2 = "" Then
            requete = requete & " WHERE O1.FOURNISSEURS='" & UCase(Filtre2) & "' "
            If Not Filtre3 = "" Then requete = requete & " AND O1.PRODUITS='" & UCase(Filtre3) & "' "
        Else
            If Not Filtre3 = "" Then requete = requete & " WHERE O1.PRODUITS='" & UCase(Filtre3) & "' "
        End If
    End If

    requete = requete & " ORDER BY O1.MOIS UNION ALL " & _
            " SELECT O2.PAYS, O2.FOURNISSEURS, O2.PRODUITS, O2.MOIS, O1.T14, O2.T15, O1.V14, O2.V15 " & _
                " FROM(SELECT  PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES, SUM(CA) AS T14, SUM(VOL) AS V14" & _
                " FROM [" & Data1 & "] " & _
                " WHERE ANNEES=2014" & _
                " GROUP BY PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES) AS O1" & _
                " RIGHT JOIN (SELECT  PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES, SUM(CA)AS T15, SUM(VOL) AS V15 " & _
                " FROM [" & Data1 & "] " & _
                " WHERE ANNEES=2015" & _
                " GROUP BY PAYS, FOURNISSEURS, PRODUITS, MOIS, ANNEES) AS O2" & _
                " ON O1.PAYS=O2.PAYS AND O1.FOURNISSEURS=O2.FOURNISSEURS AND O1.PRODUITS=O2.PRODUITS"

    If Not Filtre1 = "" Then
        requete = requete & " WHERE O1.PAYS='" & UCase(Filtre1) & "' "
        If Not Filtre2 = "" Then requete = requete & " AND O1.FOURNISSEURS='" & UCase(Filtre2) & "' "
        If Not Filtre3 = "" Then requete = requete & " AND O1.PRODUITS='" & UCase(Filtre3) & "' "
    Else
       If Not Filtre2 = "" Then
            requete = requete & " WHERE O1.FOURNISSEURS='" & UCase(Filtre2) & "' "
            If Not Filtre3 = "" Then requete = requete & " AND O1.PRODUITS='" & UCase(Filtre3) & "' "
        Else
            If Not Filtre3 = "" Then requete = requete & " WHERE O1.PRODUITS='" & UCase(Filtre3) & "' "
        End If
    End If

    requete = requete & " ORDER BY O1.MOIS "

Pierre

Le 20/05/2019 à 09:36

bonjour,

merci pour ta réponse finalement ce n'était pas la longueur de la requête le soucis, la msgbox n'affichait juste pas tout les caractères de ma requête. En plus j'avait 2-3 erreurs parsemées dans celle-ci

Dans tout les cas merci pour ton aide et tes fonctions

Rechercher des sujets similaires à "access"