Insérer des enregistrements Excel sous Access via VBA

**rose35** · 18/05/2017, 09h28

Bonjour,

Je souhaite insérer des données Excel dans une base de données Access via un code VBA. Mon excel contient un tableau avec 5 colonnes : date, réfproduit, marque, quantité, identifiant. Idem sur ma table Access.

Cependant mon code ne marche pas, j'ai l'erreur : "Erreur d'exécution '-2147217913 (80040e07)' - Type de données incompatible dans l'expression du critère".

Sauriez-vous m'aider s'il vous plait?
J'ai bien fait attention à mettre mes données Excel et Access sous le même format. Il semble que la ligne SELECT*FROM ..... WHERE pose problème.

Merci par avance pour votre aide.

Rose

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub WritingWorksheetExcel_Access()
 
    Dim DATABASE As String
    DATABASE = "G:\AccessRose\Basededonnees1.accdb"
 
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    Dim rs2 As Object
    Set rs2 = CreateObject("ADODB.Recordset")
    Dim confirm As Integer
    Dim r As Integer
 
    confirm = MsgBox("Voulez-vous mettre à jour la base Access ? ", vbOKCancel)
 
    If confirm <> 1 Then
        Exit Sub
    End If
 
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=" & DATABASE & ";"
    ' open a recordset
    'num de ligne = 2
    r = 2
    For r = 2 To 10000
        If Range("A" & r).Value = "" Then
            Exit For
        End If
 
        Set rs = New ADODB.Recordset
        Dim statement As String
        statement = "SELECT * FROM   [AccessRose]  " & _
        " WHERE ( (Dates = '" & Range("A" & r).Value & Format(Dates, "dd/mm/yyy") & "') AND (RefProduit = '" & Range("B" & r).Value & "') AND (Marque = '" & Range("C" & r).Value & "') AND (Quantite = '" & Range("D" & r).Value & "') AND (Id = '" & Range("E" & r).Value & "'))"
 
        rs.Open statement, _
            cn, _
            adOpenStatic, _
            adLockOptimistic, _
            adCmdText
 
 
        If rs.EOF Then
            ' create a new record
            ' add values to each field in the record
            rs.AddNew
        End If
 
        With rs
        .Fields("Dates") = Range("A" & r).Value
        .Fields("RefProduit") = Range("B" & r).Value
        .Fields("Marque") = Range("C" & r).Value
        .Fields("Quantite") = Range("D" & r).Value
        .Fields("Id") = Range("E" & r).Value
        .Update
        End With
    Next r
 
 
    cn.Close
    Set cn = Nothing
    rs.Close
    Set rs = Nothing
    rs2.Close
    Set rs2 = Nothing
 
 
End Sub

Invité · 18/05/2017, 10h28

Bonjour,
effectivement attention aux type de champ de la base en fonction des valeurs excel!

ID numérique? ID=1 ou ID='1' (String); Quantite =10 ou Quantite ='10'

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub WritingWorksheetExcel_Access() 
   Const DATABASE = "G:\AccessRose\Basededonnees1.accdb": Dim R As Integer
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DATABASE & ";"
         With .Execute("SELECT * FROM   [AccessRose]")
               For R = 2 To 10000
                    .Filter = "(Dates = '" & Range("A" & R).Value & Format(Dates, "dd/mm/yyy") & "') AND (RefProduit = '" & Range("B" & R).Value & "') AND (Marque = '" & Range("C" & R).Value & "') AND (Quantite = '" & Range("D" & R).Value & "') AND (Id = '" & Range("E" & R).Value & "'))"
                    If .EOF Then .AddNew
                        .Fields("Dates") = Range("A" & R).Value
                        .Fields("RefProduit") = Range("B" & R).Value
                        .Fields("Marque") = Range("C" & R).Value
                        .Fields("Quantite") = Range("D" & R).Value
                        .Fields("Id") = Range("E" & R).Value
                        .Update
                    Next
            .Close
         End With
         .Close
    End With
End Sub

**rose35** · 18/05/2017, 10h33

Merci pour votre aide

Id est sur Excel un numérique et sur Access un numéro automatique.

Invité · 18/05/2017, 10h37

donc seul id est utile pour filtré puisque univoque!

il ne peut être ni ajouté ni Updaté!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 .Filter = "Id =" & Range("E" & R).Value
                  If .EOF Then .AddNew
                        .Fields("Dates") = Range("A" & R).Value
                        .Fields("RefProduit") = Range("B" & R).Value
                        .Fields("Marque") = Range("C" & R).Value
                        .Fields("Quantite") = Range("D" & R).Value
                       ' .Fields("Id") = Range("E" & R).Value 
                        .Update
                       Range("E" & R).Value= .Fields("Id") 'je récupère dans Excel ID qui s'incrémente automatiquement dans ma table en cas de création (.AddNew)

https://mon-partage.fr/f/LoCWKVjq/

**rose35** · 18/05/2017, 10h57

Après la mise à jour de mon code :Erreur de compilation Next sans For
Pourtant j'ai mon For pour R donc je ne comprends pas

EDIT :
J'ai finalement l'erreur 3001 :

Les argument sont de type incorrect ,en dehors des limites autorisées ou en conflit les uns avec les autres.

C'est la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Filter ="Id=" & Range("E"&R).Value & ""

Qui beug.

J'ai modifié R entre 2 et 12 et là ça marche mais quand je veux recommencer cela me dit que ce n'est pas possible les mises à jour.

Invité · 18/05/2017, 11h58

Id est un champ unique, tu ne peux pas avoir plusieurs fois la même valeurs dans ce champs, si tu as déjà ajouté l'id E2 à E12 tu ne peux pas les rajouter à nouveau ou les modifier. De plus, tu n'as pas du mettre en commentaire la ligne de code qui pose problème comme l'a fait dysorthographie.

**rose35** · 18/05/2017, 12h00

Ah effectivement! Mais du coup comment faire pour créer un nouvel en enregistrement Access numéroté 13 si je rajoute une ligne sur Excel à la suite des autres svp?

Invité · 18/05/2017, 11h58

désolé!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub WritingWorksheetExcel_Access()   Const DATABASE = "G:\AccessRose\Basededonnees1.accdb": Dim R As Integer, Cn As Object
   Set Cn = CreateObject("ADODB.Connection")
    With Cn
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DATABASE & ";"
         With CreateObject("ADODB.Recordset")
         .Open "SELECT * FROM   [AccessRose]", Cn, 1, 3
               For R = 2 To 10000
                     .Filter = "ID=" & IIf(Trim("" & Range("E" & R).Value) = "", "Null", Range("E" & R).Value)
                    If .EOF Then .AddNew
                        .Fields("Dates") = Range("A" & R).Value
                        .Fields("RefProduit") = Range("B" & R).Value
                        .Fields("Marque") = Range("C" & R).Value
                        .Fields("Quantite") = Range("D" & R).Value
                        .Update
                         Range("E" & R).Value = .Fields("Id")
                    Next
            .Close
         End With
         .Close
    End With
End Sub

Invité · 18/05/2017, 12h09

c'est prévu là!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 .Update
Range("E" & R).Value = .Fields("Id")

tu laisses Range("E" & R).Value à vide le programme s'en chargera! .Filter = "ID=" & IIf(Trim("" & Range("E" & R).Value) = "", "Null", Range("E" & R).Value)

**rose35** · 18/05/2017, 14h54

Merci beaucoup pour vos aides

Cependant, là des lignes vides se sont ajoutées sur mon Excel et le code tourne tourne... Cela ajoute 10000 lignes en fait. Je vais donc mettre une contrainte : arrêter d'ajouter des enregistrements quand les autres champs (ref, date, ...) sont vides

Invité · 18/05/2017, 15h07

voila ton code!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For R = 2 To 10000

et voila ce qu'il faut mettre!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For R = 2 To Range("A1").CurrentRegion.Rows.Count

**rose35** · 18/05/2017, 15h14

Parfait, merci beaucoup!! Tout marche

Insérer des enregistrements Excel sous Access via VBA

Macros et VBA Excel

Vue hybride

Partager

Partager