VBA Import et export table Access dans Excel

Version imprimable

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 ...

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.
22/09/2018, 15h05
Rémy.A

VBA Import et export table Access dans Excel

Bonjour Chris,

Merci pour toutes c'est explications :)

VBA Import et export table Access dans Excel

Bonjour à tous,

Suite aux messages précédents, j'ai lié les tables Access entres elles mais cela me semble ne pas être la bonne solution, à chaque fois que j'ai un nouveau fichier d'un nouveau client il faut tous recommencer...

J'ai posté dans les message précédent que j'aimerai une macro Excel qui récupère les données le la table sélectionnée de mes différents fichier mdb, en face une seule table triée. Puis Excel vide la table en question (dans le fichier Access) et ré-écrit la table dans les différents mdb.

Actuellement, je n'arrive pas à trouver le code pour écrire dans la table Access..
Voici le code que j'ai fais:

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
71
72
73
74
75
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
 
'Récupère le nombre de tables pour répéter la boucle
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
 
'Pour copier la table dans Excel
 
FichierExcel = ActiveWorkbook.Name
Workbooks(FichierExcel).Activate
Sheets("Feuil7").Select
Cells(2, 1).CopyFromRecordset rst
Sheets("Feuil1").Select
 
'Plage de sélection des données à remplacer dans la  table
Range(Cells(2, 1), Cells(Range("A65536").End(xlUp).Row, Range("XFD2").End(xlToLeft).Column)).Select ' Permet de sélectionner des lignes et des colonnes sans connaitre la fin
 
'Voila c'est ici que je bute pour créer le code pour écrire les données dans la table.
 
rst.Close
cnt.Close
 
Next i
End Sub

Merci pour votre aide!!

Bonjour,

J'ai travaillé sur mon code.
J' arrive à me connecter et à récupérer les données et à vider ma table.

Quand je veux insérer mes nouvelles données j' ai un message d'erreur sur la syntaxe de INSERT INTO :mur:

C'est à la ligne 152 et 153 du code :

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
Sub Import_AccessData()
 
' Transfert les données de la feuille Excel vers une table Access
' Activer référence Microsoft ActiveX Data Objects 6.1 Library
Dim Cn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strCn As String
Dim strInsert As String
Dim strUpdate As String
Dim r As Long
 
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 CheminAccess As String
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 pour faire la boucle en f(j)
Dim j As Integer                        'C'est un compteur pour lister les table
Dim x As Integer                        'C'est un compteur pour les trois fichiers
Dim y As Integer                        'C'est un compteur pour inserer les ligne
Dim Table As String
Dim Derniere_ligne As Integer
 
 
'############################################################################################################################
'############################################################################################################################
'############################################################################################################################
 
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 = 8 To j 'Boucle pour récupérer les noms des tables à extraire vers Excel
 
        Table = Range("A" & i)
 
        For x = 1 To 3 'Boucle pour récupérer les les tables à extraire vers Excel des 3 fichiers
 
                If x = 1 Then
                CheminAccess = CheminF1
                Else
                If x = 2 Then
                CheminAccess = CheminF2
                Else
                If x = 3 Then
                CheminAccess = CheminF3
                Else
                End If
                End If
                End If
 
                ' Connection à la base Access
                Set Cn = New ADODB.Connection
                strCn = "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=" & CheminAccess & ";"
                Cn.Open strCn
 
 
                ' Ouverture recordset / table
                ' Objet Recordset représente les enregistrements d'une table
                Set rs = New ADODB.Recordset
                rs.Open "SELECT * FROM" & "[" & Table & "]", Cn, adOpenKeyset, adLockOptimistic
 
 
 
                FichierExcel = ActiveWorkbook.Name
                Workbooks(FichierExcel).Activate
                Sheets("Feuil3").Select
                Cells(Cells.Columns.Count, 1).End(xlUp).CopyFromRecordset rs
                Sheets("Feuil1").Select
 
 
                ' Fermeture recordset et connection
                rs.Close
                Set rs = Nothing
                Cn.Close
                Set Cn = Nothing
 
         Next x
 
        '*************************************************************************************************
        ' Insérer code pour trier les tables de A-Z puis supprimer les doublons!!!
 
 
 
        '*************************************************************************************************
        ' Une fois le code précédent inséré, faire code pour vider la table et la remplir
 
 
 
        'Tables avec entêtes de colonnes identiques
        If Table = "ARMOIRE" Or Table = "RELMAGN" Then
 
        'Dernière ligne du fichier Excel
        Derniere_ligne = Cells(Cells.Columns.Count, 1).End(xlUp).Row
 
        For x = 1 To 3
 
            If x = 1 Then
            CheminAccess = CheminF1
            Else
            If x = 2 Then
            CheminAccess = CheminF2
            Else
            If x = 3 Then
            CheminAccess = CheminF3
            Else
            End If
            End If
            End If
 
            ' Connection à la base Access
            Set Cn = New ADODB.Connection
            strCn = "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=" & CheminAccess & ";"
            Cn.Open strCn
            ' Ouverture recordset / table
            ' Objet Recordset représente les enregistrements d'une table
            Set rs = New ADODB.Recordset
            rs.Open "SELECT * FROM" & "[" & Table & "]", Cn, adOpenKeyset, adLockOptimistic
 
            'Supprimmer le contenu de la table
            Cn.Execute "Delete * FROM" & "[" & Table & "]"
 
 
 
 
            For y = 1 To Derniere_ligne
 
                strInsert = "INSERT INTO Table (CODE, LIBELLE, FABRICANT, SERIE, PRODUIT, RESUME_CONTACTS, DX,  DY,  DZ,  POIDS,   CODE_INT,    CODE_EAN,    PRIXHT,  UNITFACT,    COLISAGE,    ACCESSOIR,   SYMBOLE, IMPLANTE,    VIGNETTE,    VUE_YZ,  VUE_XZ,  EQUIPEMEN,   DATE,    WEB PICTURE, {SEE_SET}, {SEE_CONTACTS_ID})VALUES ('" & Range("A1").Offset(i, 0).Value & "', #" & Range("A1").Offset(i, 1).Value & "#, '" & Range("A1").Offset(i, 2).Value & "', '" & Range("A1").Offset(i, 3).Value & "','" & Range("A1").Offset(i, 4).Value & "','" & Range("A1").Offset(i, 5).Value & "','" & Range("A1").Offset(i, 6).Value & "','" & Range("A1").Offset(i, 7).Value & "','" & Range("A1").Offset(i, 8).Value & "','" & Range("A1").Offset(i, 9).Value & "','" & Range("A1").Offset(i, 10).Value & "','" & Range("A1").Offset(i, 11).Value & "','" & Range("A1").Offset(i, 12).Value & "','" & Range("A1").Offset(i, 13).Value & "','" & Range("A1").Offset(i, 14).Value & "','" & Range("A1").Offset(i, 15).Value _
                & "','" & Range("A1").Offset(i, 16).Value & "','" & Range("A1").Offset(i, 17).Value & "','" & Range("A1").Offset(i, 18).Value & "','" & Range("A1").Offset(i, 19).Value & "','" & Range("A1").Offset(i, 20).Value & "','" & Range("A1").Offset(i, 21).Value & "','" & Range("A1").Offset(i, 22).Value & "','" & Range("A1").Offset(i, 23).Value & "','" & Range("A1").Offset(i, 24).Value & "','" & Range("A1").Offset(i, 25).Value & "','" & Range("A1").Offset(i, 26).Value & "','" & Range("A1").Offset(i, 27).Value & "') "
                Cn.Execute strInsert
 
            Next y
 
        ' Fermeture recordset et connection
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
 
        Next x
 
        Else
 
        If Table = "HF" Or Table = "HP" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
 
        If Table = "UCSCONTA" Or Table = "UCSCORPS" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
 
        If Table = "BDJACC" Or Table = "DETEC" Or Table = "DISTRIB" Or Table = "DIVERS" Or Table = "ELECTRO" Or Table = "FILTRE" Or Table = "GOULOTTE" Or Table = "INDEC" Or Table = "MOTEUR" Or Table = "PATHWAY" Or Table = "PC" Or Table = "POMPE" Or Table = "RAIL" Or Table = "REGUL" Or Table = "REPARTIT" Or Table = "REPERAGE" Or Table = "UCSACC" Or Table = "VANNE" Or Table = "VARIAT" Or Table = "VERIN" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
 
        'Tables avec entêtes de colonnes non identique
        If Table = "ALIM" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
 
        If Table = "API" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
 
        If Table = "BDJ" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "BROCHE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "BUS" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "CABLE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "CARTE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "COMMUT" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "Connect" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "CONTAUX" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "CONTPUI" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "DEMARRE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "DEPARTMOT" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "DISJIND" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "FUSIBLE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "INTERRUP" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "MESURE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "MODSECU" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "PARAFOUDRE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "RACK" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
 
 
        If Table = "RELAIS" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "RELSUR" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "SECTIO" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "SOCLE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "TC" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "TRANSFO" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "UCS" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "UCSLAMP" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "UCSTETE" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        Else
        If Table = "WT" Then
        '*************************************************************************************************
        'Faire la formule SQL pour se connecter
 
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
 
 
 
         '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
 
 
 
 
 
 
' Fermeture recordset et connection
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End ' Pour faire le teste en pas à pas je quitte la macro pour ne pas faire la boucle
Next i
End Sub

Si quelqu'un peut m'aider ça serai cool 8-)8-)
Merci par avance!!

[XL-2016] VBA Import et export table Access dans Excel

Bonjour,

J'ai chercher l'erreur.

J'ai ré-écris la requête en utilisant des variables tampon, maintenant j'ai une erreur de syntaxe dans l'expression '25';

Code:

1
2
3
4
5
6
 
 
strInsert = "INSERT INTO [" & Table & "] (CODE, [LIBELLE], [FABRICANT], [SERIE], [PRODUIT], [RESUME_CONTACTS], [DX],  [DY],  [DZ],  [POIDS], [CODE_INT],[CODE_EAN],[PRIXHT],[UNITFACT], [COLISAGE],[ACCESSOIR], [SYMBOLE], [IMPLANTE],[VIGNETTE], [VUE_YZ], [VUE_XZ], [EQUIPEMEN],[DATE], [WEB PICTURE], [{SEE_SET}], [{SEE_CONTACTS_ID}])" & _
" VALUES ('" & T1 & "', " & T2 & ", '" & T3 & "', '" & T4 & "','" & T5 & "','" & T6 & "','" & T7 & "','" & T8 & "','" & T9 & "','" & T10 & "','" & T11 & "','" & T12 & "','" & T13 & "','" & T14 & "','" & T15 & "','" & T16 & "','" & T17 & "','" & T18 & "','" & T19 & "','" & T20 & "','" & T21 & "','" & T22 & "','" & T23 & "','" & T24 & "', ' " & T25 & " ';) "
Cn.Execute strInsert
'

[XL-2016] VBA Import et export table Access dans Excel

Re,

En réfléchissant ,en imaginant, en essayant, et en testant, j'ai trouvé une autre méthode pour renseigner la table dans Access qui est beaucoup plus efficace et simple a codé!!:mouarf:

En plus de cette façon, j'ai remarqué que l'on peut renseigner le nombres de champs que l'on veux sans avoir de bugs ou d'erreurs!!

Ci-dessous le code pour qui ça intéresse.

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
 
 
 
Dim Cn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strCn As String
 
 
           ' Connection à la base Access
            Set Cn = New ADODB.Connection
            strCn = "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=" & CheminAccess & ";"
            Cn.Open strCn
 
            ' Ouverture recordset / table
            ' Objet Recordset représente les enregistrements d'une table
            Set rs = New ADODB.Recordset
            rs.Open "SELECT * FROM" & "[" & Table & "]", Cn, adOpenKeyset, adLockOptimistic
 
            'Supprimmer le contenu de la table
            Cn.Execute "Delete * FROM" & "[" & Table & "]"
 
        With rs
            ' Créer un enregistrement
            .AddNew
            ' ajouter les valeurs à chaque champ de l'enregistrement
            .Fields("CODE") = T1
            .Fields("LIBELLE") = T2
            .Fields("FABRICANT") = T3
            .Fields("SERIE") = T4
            .Fields("PRODUIT") = T5
            .Fields("RESUME_CONTACTS") = T6
            .Fields("DX") = T7
            .Fields("DY") = T8
            .Fields("DZ") = T9
            .Fields("POIDS") = T10
            .Fields("CODE_INT") = T11
            .Fields("CODE_EAN") = T12
            .Fields("PRIXHT") = T13
            .Fields("UNITFACT") = T14
            .Fields("COLISAGE") = T15
            .Fields("ACCESSOIR") = T16
            .Fields("IMPLANTE") = T17
            .Fields("VIGNETTE") = T18
            .Fields("VUE_YZ") = T19
            .Fields("VUE_XZ") = T20
            .Fields("DATE") = T21
            .Fields("WEB") = T22
            .Fields("PICTURE") = T23
            .Fields("{SEE_SET}") = T24
            .Fields("{{SEE_CONTACTS_ID}}") = T25
            .Update
        End With
 
       ' Fermeture recordset et connection
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing

On est jamais mieux servi que pars sois même.

La connaissance s’acquiert pars l'expérience , le reste n'est que de l'information. Albert Einstien.