[Sources] [VBA] Importation sélective Excel -> Access sans doublons

[azertix]

Bonjour à tous !

Alors, ce code résume la discussion que j'ai eu avec totofe et présente les dernières avancées auxquelles nous sommes arrivés, grâce à son entière contribution.
Vu que c'est un thème assez abordé et que l'importation Excel -> Access est souvent utilisée, j'ai pensé que proposer ce code dans les sources pourrait aider pas mal de développeurs en herbe .

Le code permet de copier les plages de cellules voulues d'un fichier Excel vers les champs voulus d'une table Access, en évitant le doublons.

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
Private Sub Commande1_Click()
'Ici l'importation se déclenche en cliquant sur le bouton "Commande1"
'mais on peut aussi mettre ce code à l'ouverture d'un formulaire
Dim oApp As Excel.Application
Dim oWkb As Excel.Workbook
Dim oWSht As Excel.Worksheet
 
Set oApp = CreateObject("excel.application")
Set oWkb = oApp.Workbooks.Open("chemin_du_fichier_xls") 'mettez ici le chemin vers votre fichier Excel
Set oWSht = oWkb.Worksheets("nom_de_la_feuille_concernée_par_limportation") 'mettez ici le nom de la feuille qui contient les données à importer
 
'première ligne ou commence l'import
i = 11
 
'pour éviter les messages lors de l'ajout des enregistrements
DoCmd.SetWarnings False
 
'tant qu'on n'est pas arrivés à la ligne 600 du tableur
While i < 600
'on peut aussi arrêter l'importation lorsque le programme rencontre une case
'vide en remplaçant la ligne du While par :
'While oWSht.Range("I" & i).Value <> "" '(où I représente la colonne et i la ligne)
 
'condition de remplissage de la table => eviter les doublons
'si l'enregistrement existe déjà dans la table destination,
'on passe à la ligne suivante sans l'importer
If DCount("*", "[nom_da_la_table_destination]", "[nom_du_champ_destination_qui_ne_doit_pas_avoir_de_doublons] LIKE '" & oWSht.Cells(i, 9) & "'") = 0 Then
'le numéro 9 correspond au numéro de la colonne source, tel que : A=1, B=2, C=3 ...
 
'requète SQL (avec en paramètre la ligne i et le numéro de la colonne comme précisé au-dessus)
cSQL = "insert into [table_destination] ( [champ1], [champ2] ) values (" & Chr(34) & oWSht.Cells(i, 13) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 11) & Chr(34) & ");"
'ici, on ne prend que les colonnes M (=13) et K (=11).
 
'exécute la requète
DoCmd.RunSQL cSQL
 
End If
 
'on incrémente la variable i pour passer à la ligne suivante
i = i + 1
 
Wend
 
'on réactive les messages d'erreurs
DoCmd.SetWarnings True
Set oWSht = Nothing
Set oWbk = Nothing
Set oApp = Nothing
End Sub

Bon, tous les commentaires ne sont là que pour la compréhension du code, mais si vous aviez toutefois des questions, n'hésitez pas

[Lou Pitchoun]

Salut,

Si tu mets

Code :

DoCmd.SetWarnings False

il faut à la fin remettre

Code :

DoCmd.SetWarnings True

Mais il y a peut être plus simple pour importer une partie des données Excel vers Access

[azertix]

Envoyé par Lou Pitchoun

Salut,

Si tu mets

Code :

DoCmd.SetWarnings False

il faut à la fin remettre

Code :

DoCmd.SetWarnings True

Message édité !
J'avais oublié le End Sub, aussi

[boubouch]

Désolé d'arriver si tard mais estce que le bouton se place dans le fichier excel ??

[azertix]

Non, personnellement, je l'ai placé sur un formulaire sous Access

[shuggy]

Bonjour. Désolé de déterrer ce post mais comme il m'a servi pour un projet, je tiens à faire part d'une remarque, tout fonctionne correctement mais ne serait-il pas judicieux de libérer la mémoire utilisée par les variables objet à la fin de la fonction?

Code :

1
2
3
Set oWSht = Nothing
Set oWbk = Nothing
Set oApp = Nothing

[azertix]

Envoyé par shuggy

Bonjour. Désolé de déterrer ce post mais comme il m'a servi pour un projet, je tiens à faire part d'une remarque, tout fonctionne correctement mais ne serait-il pas judicieux de libérer la mémoire utilisée par les variables objet à la fin de la fonction?

Code :

En effet. J'étais tout jeune développeur à l'époque de ce code, je faisais ça crado

Par contre il semblerait que l'on ne puisse pas éditer un message trop ancien ?

[Pierre Fauconnier]

Salut.

Il serait peut-être judicieux de rédiger le code de façon à le rendre générique. En gros, il faudrait retirer les variables "externes" (chemin du fichier, plages, etc...) et les passer en paramètres.

Cela permet une réutilisation aisée du code, sans devoir se poser la question de savoir où on la place (Excel ou Access, déclenchement par bouton ou "en direct", etc)...

[azertix]

Envoyé par Pierre Fauconnier

Salut.

Il serait peut-être judicieux de rédiger le code de façon à le rendre générique. En gros, il faudrait retirer les variables "externes" (chemin du fichier, plages, etc...) et les passer en paramètres.

Cela permet une réutilisation aisée du code, sans devoir se poser la question de savoir où on la place (Excel ou Access, déclenchement par bouton ou "en direct", etc)...

Bonjour Pierre.
Le problème c'est qu'on ne peut pas entièrement rendre ce code "générique" puisque qu'il s'agit d'une importation sélective.
Il y aura dans tous les cas des modifications à faire dans le code.
De plus, bien que le découpage d'un projet en sous-procédures et fonctions soit la bonne méthode de coder, je crains qu'un débutant ne soit pas familier avec leur utilisation (appel, arguments, retour...).

À toutes fins utiles, voici ce que je peux fournir rapidement :

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
Function ImportXL(xlPath As String, wsName As String, startRow As Integer, pKeyCol As String, acTable As String, pKey As String) As Boolean
'-> La fonction renvoie vrai si l'import réussit et faux dans le cas contraire
'xlPath : chemin du fichier Excel
'wsName : nom de la feuille Excel qui contient les données à importer
'startRow : ligne du fichier Excel où commence l'import
'pKeyCol : colonne du fichier Excel qui est la clé primaire de la table Access
'acTable : table Access qui reçoit les données
'pKey : nom du champ "identifiant"
 
    'active la routine de gestion d'erreur.
    On Error GoTo erreur
 
    'déclaration des variables
    Dim app As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
 
    'initialisation des variables
    Set app = New Excel.Application
    Set wkb = app.Workbooks.Open(xlPath)
    Set wks = wkb.Worksheets(wsName)
 
    Dim i As Integer, cSQL As String
    i = startRow
 
    'pour éviter les messages lors de l'ajout des enregistrements
    DoCmd.SetWarnings False
 
    With wks
        'arrêter l'importation lorsque l'on rencontre une case vide
        While .Range(pKeyCol & i).Value <> "" '(où pKeyCol représente la colonne et i la ligne)
 
            'condition de remplissage de la table => eviter les doublons
            'si l'enregistrement existe déjà dans la table destination,
            'on passe à la ligne suivante sans l'importer
            If DCount("*", acTable, pKey & " LIKE '" & .Range(pKeyCol & i).Value & "'") = 0 Then
 
                'requête SQL (ajouter autant de champs que nécessaire)
                cSQL = "INSERT INTO " & acTable & " ( [champ1], [champ2] ) VALUES (" & Chr(34) & .Range("E" & i) & Chr(34) & ", " & Chr(34) & .Range("G" & i) & Chr(34) & ");"
                'exemple avec les colonnes E et G
 
                'exécute la requète
                DoCmd.RunSQL cSQL
 
            End If
 
            'on incrémente la variable i pour passer à la ligne suivante
            i = i + 1
        Wend
 
    End With
 
    'on réactive les messages d'erreurs
    DoCmd.SetWarnings True
 
    'libération variables
    Set wks = Nothing
    Set wkb = Nothing
    Set app = Nothing
 
    MsgBox "Import du fichier Excel réussi.", vbInformation + vbOKOnly, "Opération terminée..."
 
    ImportXL = True
    Exit Function
 
erreur:    ' Routine de gestion d'erreur.
    MsgBox "Erreur: " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbInformation
    ImportXL = False
End Function

J'ai abondement commenté et ai ajouté un gestionnaire d'erreur.
Le code a été correctement compilé mais il n'a pas été testé.

[princeflorizel]

Bonjour,
j'ai repiqué et adapté ce code, qui est très utile, par contre je rencontrais une erreur de type "définie par l'application..",
Je ne sais pas si c'est moi qui est mal ré-adapté mais en passant la variable Pkeycol en string, et en la definissant par sa lettre de colonne, ça passe...
A noter que mon stlyle de référence sous Excel n'est pas définie en L1C1, peut être cela venait de cela, je n'ai pas testé...Slts

[azertix]

Envoyé par princeflorizel

Bonjour,
j'ai repiqué et adapté ce code, qui est très utile, par contre je rencontrais une erreur de type "définie par l'application..",
Je ne sais pas si c'est moi qui est mal ré-adapté mais en passant la variable Pkeycol en string, et en la definissant par sa lettre de colonne, ça passe...
A noter que mon stlyle de référence sous Excel n'est pas définie en L1C1, peut être cela venait de cela, je n'ai pas testé...Slts

Il faut effectivement mettre cette variable en string au lieu d'integer.
Je viens de demander la modification.
Merci de l'avoir signalé

Edit : C'est bon, modif faite par Pierre Fauconnier.

[lakhdar16]

bonjour,

cette discussion est très intéressante pour moi, ce code m'a beaucoup aidé à réaliser ce que je veux.

juste une petite question:

dans mon cas le code sert à importer des données de type numérique, texte, date et booléen dans ma base de données (dorsale/frontale), si le client fait une erreur de saisie dans le fichier Excel par exemple: il met une valeur de type texte au lieu de type numérique, ou il saisi une valeur avec un autre format, comment puis-je éviter l'erreur lors de l'importation en cas de erreur de saisie et convertir les format en cas de format différente dans le fichier Excel?

J'espère que j'été claire dans ma question.

merci à vous.

[lakhdar16]

Bonsoir,

peut-on associer un progressBar avec cette fonction?

merci.