VBA Import et export table Access dans Excel

Version imprimable

Voir 40 message(s) de cette discussion en une page

20/09/2018, 17h08
Rémy.A

1 pièce(s) jointe(s)

VBA Import et export table Access dans Excel

Bonjour à tous,

Je suis un peux plus que débutant (mais pas trop :mrgreen:)en VBA Excel...

Au travail j'utilise un logiciel de schémas électriques ( On a 3 versions différentes) qui à un environnement. Cet environnement à un catalogue matériel qui est géré avec un fichier "Equipement.mdb". J'ai plus de 800 références matériel à créer sur chaque versions de logiciel, sois je passe par le logiciel (ce qui prendra des mois...), sois je modifie le fichier mdb. J'ai choisi de modifier le mdb.

Le problème est que je ne connais rien en Access et en SQL...
J'ai donc choisis de faire une macro VBA sous Excel pour importer les tables (de chaque fichier mdb(x3)) dans excel, de les trier sur excel et de les injecter dans les 3 fichiers Access . C'est pour fusionner mes tables en gérant les doublons.

Quand j'utilise l’enregistreur de macro pour importer une table, le résulta me convient. Mais le code m'est incompréhensible pour que je puisse le travailler et faire mes boucle dans ma macro....

Après avoir fouillé sur le net, j'ai commencé à faire du code pour importer un table sur Excel, mais je voudrais avoir les entêtes des colonnes ds Excel.

Est-ce que mon code est bien? On peux faire plus simple?

C'est possible d'avoir une solution pour supprimer (vider) une table Access et de la recréer (remplir) avec Excel? Là je bute :aie::aie:

J'utilise la méthode AOD (je n'arrive pas à le faire avec la méthode DAO):weird:

Ci joint mon fichier Excel et Access

MERCI par avance pour votre aide:):););)
20/09/2018, 17h41
curt

Bonjour Rémy.A,

pourquoi ne pas lier les tables Access dans Excel ? Y effectuer les traitements souhaités et ensuite de retourner le tout dans une base Access "MiseAJour" ?
Tu éviterais du code (que tu ne maîtrises peut être pas efficacement) et une maintenance fastidieuse.

Tu pourrais aussi tout faire dans Access via une requête (l'éditeur SQL est là pour ça) pas si compliqué à faire pour le traitement de doublons, fusion, etc.

Curt
20/09/2018, 18h36
78chris

Bonjour

Si j'ai bien compris tu veux fusionner sans doublons l'ensemble des 3 tables access ?

J’imagine que chaque table a une clé primaire

Crée une base Access vierge
Fais une liaison des 3 tables (une de chacun des 3 mdb)

Copie la structure d'une des table liées (sans les données) sous le nom de Ensemble par exemple puis tu crées un requête union ainsi (en affichant le requêteur en mode sql)

Select * from Table1 union Select * from Table2 union Select * from Table3;
Tu enregistres ta requête sous le nom tout par exemple

Puis tu crées une requête ajout qui ajoute tout dans Ensemble.

Et c'est fini (cela prends moins de temps que de l'écrire ici :ptdr:)

A chaque fois que tu dois faire cela, il suffit de vider la table Ensemble et de relancer la requête ajout...
20/09/2018, 18h58
Rémy.A

Pour le forum j'ai enlevé des tables, il y a plus de 40 tables par fichiers et au total il y a plus de 400000 références voir plus.
20/09/2018, 19h25
Rémy.A

VBA Import et export table Access dans Excel

J'aimerai avoir un fichier Excel qui fusionne les trois fichiers mdb en cliquant sur un bouton afin que les collègues de travail qui l'utiliseront ne puissent pas corrompre les fichiers mdb.
De plus dans les fichiers mdb certaines tables sont différentes suivant les versions du logiciel et doivent le rester. Si un utilisateur se trompe de table en fusionnant, certaines fonctionnalités du logiciel ne fonctionneront plus...
Et à la base nous sommes électriciens et c'est tous juste s'il savent utiliser Excel et la plupart (dont moi même) nous ne connaissons pas Access.
20/09/2018, 20h39
78chris

RE

Ce que j'ai expliqué est à faire UNE fois.

Ensuite il suffit de chaîner la requête de vidage de Ensemble et la requête Ajout dans une macro Access : un bouton associé à la macro, c'est tout.

Reste à voir ce que l'on fait de la synthèse obtenue qui dans ce que j'ai décrit n'est PAS dans une des bases du logiciel (relis attentivement...).

Le nombre de référence n'a pas d'importante si justement on bosse dans Access et pas Excel où on dépasserait peut-être la limite gérable...
C'est un non sens de vouloir faire cela dans Excel à part pour déclencher le processus si tu ne veux pas que l'on voit se qui se passe dans Access...

Même s'il y a 40 tables dans les bases, les références sont dans une table, oui ou non ?

Si non, trop simplifier le problème pour demander de l'aide et donc s'éloigner de la réalité n'est pas la meilleure approche.

Donc donne toutes les billes utiles pour une solution qui répond à la problématique pas de bouts d'infos ...

VBA Import et export table Access dans Excel

RE,

Ah d'accord je ne savais pas qu'il fallait le faire une seule fois.

Si un client nous passe sont environnement, est-ce qu'il faudrait refaire les lien? Je pense que oui.

J'ai compté, il y a en tout 126 tables. Les références matériels sont réparties dans 56 tables.

J'ai mis en pièce joint un fichier mdb et un excel. J'ai réduis le fichier mdb car il fait plus de 3Go.
Sur la feuille 1 du excel il y a les 56 tables de listées ou les références sont rangées.

Dans la macro j' ai pensé :

Faire une boucle pour chaque tables

récupérer les références du 1er fichier mdb et les mettre ds excel
récupérer les références du 2eme fichier mdb et les mettre ds excel à la suite
récupérer les références du 3eme fichier mdb et les mettre ds excel à la suite

Trier les doublons

prendre toutes les références ds la feuilles excel
les mettre ds la table correspondante des trois fichier mdb

et répéter 55 fois l’opération.

Je pense que c'est le plus simple et le plus rapide. Non?

Voici le code que j'ai commencé:

Code:

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
Sub Import_AccessData()
 
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stDB As String
Dim wsSheet As Worksheet
Dim lnNumberOfField As Long
Dim lnCount As Integer
Dim FichierExcel As String
Dim Chemin As String                    'Chemin du fichier Excel
Dim Chemin1 As String                   'Chemin du fichier mdb V3R7
Dim Chemin2 As String                   'Chemin du fichier mdb V4R1
Dim Chemin3 As String                   'Chemin du fichier mdb V4R2
Dim CheminF1 As String                  'Chemin du fichier mdb V3R7
Dim CheminF2 As String                  'Chemin du fichier mdb V4R1
Dim CheminF3 As String                  'Chemin du fichier mdb V4R2
Dim Fichier1 As String                  'Fichier mdb V3R7
Dim Fichier2 As String                  'Fichier mdb V4R1
Dim Fichier3 As String                  'Fichier mdb V4R2
Dim i As Integer                        'C'est un compteur
Dim j As Integer                        'C'est un compteur
Dim x As Integer                        'C'est un compteur
Dim Table As String
 
 
FichierExcel = ActiveWorkbook.Name
Workbooks(FichierExcel).Activate
Sheets("Feuil1").Select
 
Chemin1 = Range("D8")
Chemin2 = Range("D9")
Chemin3 = Range("D10")
Fichier1 = Range("D13")
Fichier2 = Range("D14")
Fichier3 = Range("D15")
CheminF1 = Chemin1 & "\" & Fichier1
CheminF2 = Chemin2 & "\" & Fichier2
CheminF3 = Chemin3 & "\" & Fichier3
 
 
 
Chemin = ActiveWorkbook.Path & "\" & FichierExcel
 
Cells(Cells.Columns.Count, 1).End(xlUp).Select 'Permet de déterminer la dernière cellule vide de la colonne 1
 
j = ActiveCell.Row 'permet de récupérer le N° de ligne dans la variable j
 
For i = 9 To j 'Boucle pour récupérer les nomS des tables à extraire vers Excel
 
Table = Range("A" & i)
 
 
stDB = CheminF1
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & stDB & ";"
rst.Open "SELECT * FROM" & "[" & Table & "]", cnt
 
 
FichierExcel = ActiveWorkbook.Name
Workbooks(FichierExcel).Activate
Sheets("Feuil7").Select
Cells(2, 1).CopyFromRecordset rst
Sheets("Feuil1").Select
 
 'Range(Cells(2, 1), Cells(Range("A65536").End(xlUp).Row, Range("XFD2").End(xlToLeft).Column)).Select' Permet de selectionner des lignes et des colonnes sans conaitre la fin
 
rst.Close
cnt.Close
End ' Pour faire le test en pas à pas je quitte la macro pour ne pas faire la boucle
Next i
End Sub

20/09/2018, 21h38
78chris

RE

Pas de pièce jointe à ton post.

Une base ne peut faire plus de 2Go (https://support.office.com/fr-fr/art...8-98c1025bb47c) donc 3GO...

Non une macro VBA ne sera absolument pas plus rapide qu'une requête: Excel n'est pas fait pour traiter des listes de données mais pour faire des calculs.

Tu as 2016, tu peux aussi utiliser PowerQuery justement ajouté à Excel pour traiter les données mais c'est un problème purement Acces et comme déjà dit l'essentiel doit être fait dans Access...

J'imagine qu'en électricité vous utiliser les bons outils et de dénudez pas les câbles avec des ciseaux à ongles...

Il faudrait voir ce qui diffère dans les 56 tables : il est sans doute possible de tous traiter en une fois puis tout reventiler à la fin mais il faudrait le e détail de la structure des tables et quelques exemple de lignes (enregistrements)

Pour l'environnement : il suffit, si les mdb ont les mêmes noms, de toujours utiliser le même dossier pour traiter : bien s'organiser...
20/09/2018, 21h50
Rémy.A

VBA Import et export table Access dans Excel

Pièce joint dans le premier poste
20/09/2018, 21h54
Rémy.A

VBA Import et export table Access dans Excel

J'ai lu de la documentation sur le PowerQuery mais je n'ai pas compris ce que sait
Est ce une méthode comme le ADo ou DAO?
20/09/2018, 22h12
Rémy.A

4 pièce(s) jointe(s)

VBA Import et export table Access dans Excel

Les 2 premiers libellés des colonnes colonne sont identiques sur toutes les tables, après les autre libellés se ne sont pas les même car les références matériels sont groupé par classes (ex: classe API = une table, classe CÂBLE = une autre table).

Pièce jointe 413476

Pièce jointe 413480

Pièce jointe 413484

Pièce jointe 413486
20/09/2018, 22h52
78chris

Re

Oui j'ai vu.

Cela démultiplie la solution proposée mais cela reste valable

Je te conseille de dupliquer une base, de tester sur une des tables ce que j'ai expliqué et de vérifier le résultat.

Tu pourras ensuite l’industrialiser.

Cependant tes 3 bases semblent avoir le même nom ce qui oblige à gérer 3 dossiers.
Il faut également vérifier les relations entre les tables existant à l'intérieur des bases car selon ces relations il n'est pas certain que l'on puisse simplement mettre à jour des 56 tables.

As-tu le schéma de structure de ces bases ?
20/09/2018, 23h28
Rémy.A

VBA Import et export table Access dans Excel

Les 3 fichiers ont l'a même structure.
Je n'est pas le schéma de structure.
Comment vérifie t-on les les relations entre les tables existant à l'intérieur des bases?
21/09/2018, 07h17
curt

Bonjour,

tu vas dans le Ruban, OUTILS DE BASE DE DONNEES / RELATIONS

As-tu regardé les imports Access dans Excel et vice-versa ?

Curt
21/09/2018, 09h03
Rémy.A

1 pièce(s) jointe(s)

VBA Import et export table Access dans Excel

Bonjour,

Oui j'ai pensé à regarder et j'ai commencer à le faire mais j'ai arrêté car c'est trop long comme solution.

En ce qui concerne les relations, j'ai regarder et je crois qu'il en n'a pas.

Voici ce qui ce passe lorsque je clique sur afficher toutes les relations
Pièce jointe 413557
21/09/2018, 09h48
78chris

Bonjour

Donc si pas de relations, tu pourras soit supprimer chaque table et la réinjecter dans sa base opérationnelle, soit injecter les données par requête ajout (avec gestion de non correspondance même si les clés primaire évitent les doublons) dans les bases opérationnelles.

Cette partie là devra être faite par VBA depuis la base Access de traitement afin d'éviter une manipulation des bases opérationnelles...
21/09/2018, 12h07
Rémy.A

VBA Import et export table Access dans Excel

Bonjour Chris,

Je vais essayer par VBA Excel de supprimer les données de la tables Access et de les ré-importer depuis Excel une fois les doublons gérés.
Je l'ai fait manuellement avec Excel. J'ai pris la plus grandes tables des fichiers et trié les doublons, Excel supporte bien l’opération est le temps d’exécution n'est pas si lent que ça. Je suis même surpris de sa rapidité:).

Quel est le code en VBA excel pour :

-Supprimer les données d'une tables?

-Exporter les données d'une feuille Excel dans une tables Access?
21/09/2018, 12h26
78chris

Re

Si tu maintiens ton projet en Excel pur, je ne peux t'aider.

J'ai suffisamment travaillé avec les 2 applis, souvent en les faisant collaborer, pour savoir choisir le meilleur outil.

Vu ce que qu'on peut construire simplement et efficacement en Access sans coder, puis invoquer avec juste 3 lignes de code, je n'ai jamais fait ce genre de code que je considère inutile et même dangereux même si sur 2016 il y a moins de risque de mauvais typage de données que sur les versions précédentes.

Je suis favorable au VBA quand il peut optimiser mais là...
21/09/2018, 13h28
Rémy.A

Chris,

Je vais faire la macro Excel. Néanmoins je vais aussi essayer de faire ce que tu as dis dans le poste N°3. Le problème et que je ne connais pas Access.
Peux-tu m'aider en m'aiguillant ds Access pour faire se que tu m'a dis dans le post N°3? Et il-y-a t-il un cours de débutant pour Access sur le forum?
Peut-on, une fois terminer, copier les liens dans un autre fichier Access qu'un client nous passe (les tables ne sont pas forcément identique)?
21/09/2018, 15h29
78chris

Re

Citation:

Envoyé par Rémy.A

...Je vais aussi essayer de faire ce que tu as dis dans le poste N°3. Le problème et que je ne connais pas Access.
Peux-tu m'aider en m'aiguillant ds Access pour faire se que tu m'a dis dans le post N°3?

Il y a peut-être quelques variantes de termes sur 2016 mais :

Créer une base Access vierge
Il suffit de lancer Access et créer une nouvelle base : bien choisir nom et Dossier. Tu as intérêt à avoir créé ce dossier et créer 3 sous-dossier pour tes 3 tables. Fermer Table1 qui apparait en cours de saisie/création et ne sert à rien
Fais une liaison des 3 tables (une de chacun des 3 mdb)
Données externes, Access, Sélectionner une des bases, Cocher lier à la source... ensuite sélectionner une ou n tables (pour commencer fais avec une). Tu obtiens donc via la liaison l'accès à la table originale. A refaire pour chacune des 3 Bases Sources en prenant bien la même table : définis-toi une règle de nommage pour bien t'y retrouver
Copie la structure d'une des table liées (sans les données) sous le nom de Ensemble par exemple
Clic doit sur une des 3 tables liées, Copier puis coller et là "Structure seulement"
puis tu crées un requête union ainsi (en affichant le requêteur en mode sql)
Onglet créer, création de requête, fermer la fenêtre qui s'ouvre pour le choix d'une table, puis en haut à gauche sélectionner affichage SQL et taper dans la fenêtre :
Select * from Table1 union Select * from Table2 union Select * from Table3;
en adaptant le nom des 3 tables
Tu enregistres ta requête sous le nom tout par exemple
Bouton disquette
Tu peux aussi l'exécuter avec le bouton Affichage en haut à gauche avant de la fermer pour voir le résultat
La requête union dédoublonne automatiquement
Puis tu crées une requête ajout qui ajoute tout dans Ensemble.
Onglet créer, Création de requête, sélectionner la requête Tout et fermer le fenêtre de choix. Dans l'onglet Outils de requête, Cliquer sur le bouton Ajout et choisir la table Ensemble (qui, si tu as bien suivi, est une structure vide)
Sélectionne en haut tous les champs de la table Tout (hors *) et glisse les dans la 1ère colonne de la partie basse : tu dois voir pour chaque champ la correspondance entre source de la requête Tout et Ajouter A
Le bouton Affichage permet de voir le résultat (en fait il affiche la requête Tout (revenir par le même bouton qui affiche alors une équerre), et le point d'exclamation lui exécute l'ajout réel de l'ensemble des enregistrements sélectionner par Tout à Ensemble.

A chaque fois que tu dois faire cela, il suffit de vider la table Ensemble et de relancer la requête ajout...
Pour vider une table on crée une requête de suppression : Onglet créer, Création de requête, sélectionner la table Ensemble.
Dans l'onglet Outils de requête, Cliquer sur le bouton Supression. Double cliquer sur l'étoile en haut dans la liste des champs de Ensemble. Enregistrer la requête RAZ_Ensemble par exemple.

Dans Access on peut chaîner de façon très simple des requêtes dans une macro (Access a un langage macro et un langage VBA distincts).

Comme il faudra du VBA pour réinjecter d'une façon ou d'une autre les données dans les table sources, autant chaîner par VBA...

Citation:

Envoyé par Rémy.A

Et il-y-a t-il un cours de débutant pour Access sur le forum?
Peut-on, une fois terminer, copier les liens dans un autre fichier Access qu'un client nous passe (les tables ne sont pas forcément identique)?

Je ne sais pas, il y a sans doute des choses. Self Access qui était une mine d'or a disparu...

Pour les tables clients si elles ont le même nom et la même structure, cela marche sinon il faut refaire la structure de la table Ensemble .
Une fois que tout est au point, on peut automatiser cette action par Vba, donc cela n'est plus un problème...

Dernière remarque dans un BD la notion de tri n'existe pas vraiment sauf pour affichage ou cas très très particuliers, donc on gagne du temps de traitement en ne triant pas puis qu’ici on charge juste des tables.

Voir 40 message(s) de cette discussion en une page