Erreur - Récupérer table Access dans Excel

Version imprimable

Bonjour à tous,

je viens vers vous car j'ai une erreur que je n'arrive pas à résoudre seul...

J'ai réalisé une macro qui importe les tables Access et qui les insert dans Excel, mais au moment de la copie j'ai une erreur : la méthode CopiFromRecordset de l'objet range a échoué...

Lorsque j'appuie sur débogage puis F5 le message revient je re clique sur débogage puis F5 et je recommence, la macro continue est la copie de la table est bon!!:cfou:

Est-ce que cela est dû au fait qu'il y a des champs nul/vide dans la table Access?

Existe-il une solution?

Voici mon 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
 
 
 
'Déclaration variables public 
 
Public Cn As ADODB.Connection
Public rs As ADODB.Recordset
Public strCn As String
 
Public CheminAccess As String
Public i As Long                           'C'est un compteur pour faire la boucle en f(j)
Public j As Long                           'C'est un compteur pour lister les table
Public k As Integer                        'C'est un conteur pour les feuilles Excel
Public x As Long                           'C'est un compteur pour les trois fichiers
Public y As Long                           'C'est un compteur pour inserer les ligne
Public Table As String                     'Nom de la table Access
Public CheminF1 As String                  'Chemin du fichier mdb V3R7
Public CheminF2 As String                  'Chemin du fichier mdb V4R1
Public CheminF3 As String                  'Chemin du fichier mdb V4R2
Public Derniere_ligne As Long
Public FichierExcel As String
Public chemin As String                    'Chemin du fichier Excel
Public Chemin1 As String                   'Chemin du fichier mdb V3R7
Public Chemin2 As String                   'Chemin du fichier mdb V4R1
Public Chemin3 As String                   'Chemin du fichier mdb V4R2
Public Fichier1 As String                  'Fichier mdb V3R7
Public Fichier2 As String                  'Fichier mdb V4R1
Public Fichier3 As String                  'Fichier mdb V4R2
Public Feuille1 As String                  'Feuille pour déclarer la table principale
Public Feuille2 As String                  'Feuille pour déclarer la table temporaire
Public Cel As Range                        'Plage de selection de comparaison
Public cellule As String                   'Contenu de la cellule
 
 
Public Declare Function GetTickCount& Lib "kernel32" ()
 
 
Sub Import_AccessData()
 
 
 
 
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
 
 
 
 
Sheets("Feuil1").Select
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
        Sheets("Feuil1").Select
        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
                End If
                If x = 2 Then
                    CheminAccess = CheminF2
                End If
                If x = 3 Then
                    CheminAccess = CheminF3
                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
 
                'On colle les valeur sur la feuille "Communs" pour faire la base de recherche.
                FichierExcel = ActiveWorkbook.Name
                Workbooks(FichierExcel).Activate
                Sheets("COMMUNS").Select
                Cells(Cells.Columns.Count, 1).End(xlUp).Select
                If x <> 1 Then
                    ActiveCell.Offset(1, 0).Select
                End If
 
               Selection.CopyFromRecordset rs
 
               ' Fermeture recordset et connection
                rs.Close
                Set rs = Nothing
                Cn.Close
                Set Cn = Nothing
 
                If x = 1 Then
                    Sheets("V3R7").Select
                End If
                If x = 2 Then
                    Sheets("V4R1").Select
                End If
                If x = 3 Then
                    Sheets("V4R2").Select
                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
 
                Workbooks(FichierExcel).Activate
                Cells(Cells.Columns.Count, 1).End(xlUp).CopyFromRecordset rs
 
                ' Fermeture recordset et connection
                rs.Close
                Set rs = Nothing
                Cn.Close
                Set Cn = Nothing
         Next x
 
Next i

Merci part avance pour votre aide8-):P

Cordialement
Rémy

Comme on ne sait jamais vraiment ce qui se cache sous "Selection", on va commencer par lever les ambigüités en virant des Select/Selection.

Remplace :

Code:

1
2
3
4
5
6
                Cells(Cells.Columns.Count, 1).End(xlUp).Select
                If x <> 1 Then
                    ActiveCell.Offset(1, 0).Select
                End If
 
               Selection.CopyFromRecordset rs

Par :

Code:

1
2
3
4
Dim RDest As Range
                Set RDest = Cells(Cells.Columns.Count, 1).End(xlUp)
                If x <> 1 Then Set RDest = RDest.Offset(1, 0)
                RDest.CopyFromRecordset rs

D'une manière générale, la plupart des Select/Selection de ton code devraient être virés.

26/11/2018, 16h03
Rémy.A

Erreur - Récupérer table Access dans Excel

Bonjour Menhir,

Merci pour la réponse, j'ai remplacé les lignes de code et il y a toujours la même erreur.

Oui je vais essayer de virer les selection, faut-il que je les remplace par des Activate et ActiveCell?
Par exemple si je remplace un

Code:

Sheets("COMMUNS").Select

par

Code:

Sheets("COMMUNS").Activate

Est-ce bien?

Je ne connais pas Access... En cherchant j'ai vu qu'il existe une fonction Nz(), est-ce que tu l'as connais?
26/11/2018, 16h25
Menhir

Citation:

Envoyé par Rémy.A

Oui je vais essayer de virer les selection, faut-il que je les remplace par des Activate et ActiveCell?
Est-ce bien?

Ca revient au même.

Il vaut mieux soit enchainer les référence du style Workbook.Worksheet.Range.Méthode que de faire
Workbook.Select
Worksheet.Select
Range.Select
Selection.Methode

C'est plus propre pour le codage, c'est plus rapide pour l'exécution et c'est plus agréable visuellement (on n'a pas l'effet saccadé produit par les Select successifs).
Quand ce n'est pas possible, le plus propre est de passer par une variable comme je l'ai fait dans le code que je t'ai transmis.

En plus, Selection est un terme ambigu : on ne sait pas si c'est une cellule ou une feuille (entre autre), ce qui peut être une source d'erreur en cas de méthode ou propriété aux noms identiques mais ayant des effets différents sur les deux types d'objet.
Personnellement, je ne fait jamais un Select sur un Range, sauf pour faire un placement en fin de code sur un objet créé.

Je sais que l'enregistreur automatique de macro en met partout mais ce n'est pas un exemple à suivre.

Pour en revenir à ton problème, il semble évident que le code lui-même n'est pas en cause. Il s'agit sans doute d'un problème avec les données.
Il faudrait que tu identifies les données qui posent problème et que tu places un test dans le code pour éviter que l'erreur soit générée.

Citation:

En cherchant j'ai vu qu'il existe une fonction Nz(), est-ce que tu l'as connais?

Non, je ne connais pas.
26/11/2018, 16h45
Rémy.A

Erreur - Récupérer table Access dans Excel

Merci pour les explications!

Je ne suis pas développeur... Je suis débutant en VBA mais j'aime travailler dans "Les règles de l'art"!!:D
Je vais reprendre mon code pour qu'il soit bien fait!!

Ok je vais essais d’exécuter mon code avec une nouvelle base Access pour voir si j'ai toujours l'erreur
Il me semble que dans la table Access, il y a des valeur au format date et binaire en plus du texte, C'est peut être ça..
28/11/2018, 12h49
Rémy.A

1 pièce(s) jointe(s)

Bonjour,

J'ai développé et testé la macro sur excel 2016, je viens de m’apercevoir que j 'ai eu cette erreur quand je les testé au travail (office 2010)...

Est-ce que cela viens des références à activer?

Voici les référence activées sur mon PC
Pièce jointe 430610

Esct-ce qu'il y une autre référence pour Excel 2010 et ou 2013?
28/11/2018, 13h16
Igloobel

bonjour,

pourquoi tu tembètes à faire tout cela ??? !!!

Access est directement disponible avec les tables et les requètes sous Excel surtout depuis 2007 et là tu parles de 2010 - 2013 - 2016

dans le ruban "Données" puis le groupe "Données Externes" tu as l'icone "Depuis Access"

en 2-3 clics tu as ta table moins de 15 secondes montre en main tu as tes données

c'est quand même bien plus facile que tout le reste

et pour ceux qui dirait que l'on a forcement la table en entier je leurs dirait qu'il suffit de faire une requête sous Access et de l'appeler sous Excel

j'ai fais cela bien des fois pour résoudre mes problèmes à mon boulot (où je suis en 2010) :ptdr:

voilà

A+
28/11/2018, 13h52
Rémy.A

Bonjour Igloobel,

J'aime coder.. Mais surtout, je dois mettre à jour le catalogue matériel (référence matériel) de l'environnement d'un logiciel et nous disposons de 3 versions du logiciel donc 3 environnements.

Le catalogue matériel est le même quelque soit la version du logiciel et est géré par un mdb.

un catalogue matériel est composé de 63 tables différentes. Identique pour les trois fichier mdb

Au début je faisais comme tu dis mais c'est trop long...

J'ai donc développé un macro (sous excel 2016) qui récupère les données de la tables 1 du fichier 1, du fichier 2 et du fichier 3 qui colle le tout dans Excel.
Je gère les doublons et créer des feuille temporaires qui me permet de définir les références manquantes dans chaque fichier.
Puis je mets à jour le fichier 1, le fichier 2, le fichier 3 en créant les références manquantes dans la table 1 du fichier 1,dans la table 1 du fichier 2 et dans la table 1 du fichier 3. Je répète l'opération 63 fois.

Et c'est sans compter les version migré du logiciel et on aura bientôt une 4eme version... J'ai pas que ça a faire au travail...:aie::aie:

C'est plus rapide et cela fonctionne très bien avec Excel 2016:mouarf::mouarf: mais ça bug sur Excel 2010 et 2013. :weird:
Voila pourquoi je m'embête!:mouarf:
02/12/2018, 14h13
Rémy.A

[XL-2016] Erreur - Récupérer table Access dans Excel

Bonjour,

Alors est ce que quelqu'un aurait une réponse??

Merci par avance!