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

**azertix** · 19/06/2007, 14h56

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 :

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
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** · 25/06/2007, 15h56

Salut,

Si tu mets

Code :

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

DoCmd.SetWarnings False

il faut à la fin remettre

Code :

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

DoCmd.SetWarnings True

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

**azertix** · 20/05/2008, 14h34

Envoyé par Lou Pitchoun

Salut,

Si tu mets

Code :

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

DoCmd.SetWarnings False

il faut à la fin remettre

Code :

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

DoCmd.SetWarnings True

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

**boubouch** · 21/05/2008, 16h13

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

**azertix** · 29/05/2008, 09h56

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

**shuggy** · 16/05/2011, 09h59

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 :

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

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

**azertix** · 18/05/2011, 10h37

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 :

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

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

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** · 27/05/2011, 16h28

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** · 30/05/2011, 16h11

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 :

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
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** · 16/06/2011, 14h56

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** · 17/06/2011, 09h49

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** · 28/01/2012, 15h50

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** · 25/02/2012, 20h04

Bonsoir,

peut-on associer un progressBar avec cette fonction?

merci.

**Plout** · 14/06/2012, 12h15

Bonjour,

La solution proposée est intéressante.
Je possède un problème similaire, cependant, lors de l'importation, ce n'est pas les doublons de valeurs des colonnes que je veux éviter mais les doublons de ligne (ligne dont tous les champs sont égaux).
Je n'arrive pas à trouver de solutions, si quelqu'un a une piste

**skk201** · 28/02/2014, 16h37

Super ce code !

j'ai moi aussi une question à poser.

Est-ce que on pourrait imaginer que le code s'applique à tout les classeurs excel dans un répertoire ?

Exemple fictif : On a des employés qui nous envoient des fiches mensuel de travail. Et avec 70 employé c'est long de faire cette Macro 70 fois. Imaginons que l'on place tout ces documents excels dans D:\horaires\

Comment nous pourrions adapter le code pour cette situation ?

Je vais chercher de mon coté aussi

**breweryfr** · 01/11/2014, 11h43

Bonjour,

1. Je viens de remarquer qu'importer une cellule EXCEL contenant le guillemet caractère " (ASCII 34) qui génère une erreur. Ce qui est évident...

Voici le contenu de la cellule: AB335216 / HOLZSUH - BIS "C" et je ne souhaite pas remplacer le caractère " Auriez-vous une piste ?

2. Si la recherche devait se faire sur 2 champs est-ce que la ligne suivante serait correcte ?
A savoir que l'on ne souhaite pas de doublon sur le couple pkey et pkey1.

If DCount("*", acTable, pKey & " LIKE '" & .Range(pKeyCol & i).Value & "' And " & pKey1 & " LIKE '" & .Range(pKeyCol1 & i).Value & "'") = 0 Then

Merci pour toute aide.

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

Contribuez

Discussions similaires

Partager

Partager