[XL-2007] Liste déroulante et base de données [Résolu]

[colapsus]

Bonjour à tous, voici que je veux faire, je possede un ficher sous Excel me permettant de remplir des devis, et pour selectionner les noms qui sont présents sous une page excel, je les ai placé dans une base de données sous access.

J'aimerais savoir si il est possible de lier une liste déroulante des noms de mes clients à la base de données access qui contiendrait les noms, prénoms, adresses etc....

Est ce possible ?

[Daniel.C]

Bonjour,

Sans macro, je pense que tu ne peux qu'importer les données Access dans Excel pour alimenter ta liste déroulante. Reste la solution macro, si ça te tente.

[colapsus]

Ce que je voudrai idéalement est que ma liste déroulante soit lié à ma base de données access.
est ce possible de faire cela ? et qu'entends tu par la solution macro ?

[Daniel.C]

Une macro qui lira les infos et alimentera un contrôle (ListBox, ComboBox ou ListView). Si on utilise un liste de validation de données, on ne peut l'alimenter qu'avec une colonne, ce qui fera qu'il faudra aller chercher à chaque fois les autres infos sur la base Access. A moins que la dimension de la table permette de la mettre dans une variable tableau.

[colapsus]

Oui mais je débute dans ce qui concerne les applications Sql sous Excel, est ce que je dois faire une commande dans le genre
Do.cmd.RunSQl avec un select ? ou c'est totalement différent ?

[Daniel.C]

Je vais essayer de te construire la macro. Donne-moi me nom des champs de la table et le nombre de lignes.

[colapsus]

Bon eh bien ma base de données contient 16 tables, celle qui doit alimenter la liste déroulante est la table "Contacts", constituées de 31 colonnes, la colonne que je veux afficher est la colonne "Nom" qui est la 11eme colonne, et qui possede actuellement 565 lignes, et qui en possedera 3000 d'ici peu de temps.

[Daniel.C]

Oui, mais bon, une fois que tu auras sélectionné un nom avec la liste déroulante, quelles sont les données que tu souhaites récupérer ? Quels champs et où sont-ils situés dans la table Access ? Pendant que tu y es, donne-moi le nom de la base et le nom de la table.

[colapsus]

Bon, en tout et pour tout, on aura deux liste déroulantes, une qui donne soit le nom de la société, soit le statut "particulier", la seconde liste déroulante quant à elle doit afficher une liste de nom, c'est à dire, lorsque le statut de la premiere liste est un particulier.

Une fois un des deux choix faits, il faudra faire apparaitre dans des cellules, l'adresse, le numéro de téléphone, le fax, et l'email.

Dans ma base de données access, sur ma table "contact", il y a 31 champs dans cet ordre :
Numéro Entreprise Sensibilité Adresse CP Ville Pays Service Titre Prénom Nom Fonction Téléphone GSM Fax Autre Email Annotations Prospect Interne Client Fournisseur Partenaire SousTraitant Origine OriginePrécision Suivi DateCréé SensibilitéNO DateModif AutresSuivis

PS : La base s'appelle Carnet.mdb

[Ormonth]

Bonjour,

demande à un modérateur (MP) de déplacer ton message vers général VBA, les contributeurs VBA Acces étant au moins aussi à mène de te donner la bonne réponse

cordialement,

Didier

[Daniel.C]

Pour Ormonth :

Ce n'est pas la mer à boire. Juste lire dans un recordset... Je vais le faire, pour le fun.

Pour Colapsus :

Tu auras sans doute une réponse dimanche au plus tard (à moins qu'un autre contributeur soit plus disponible, ce que je te souhaite).

J'avais survolé ceci.

une qui donne soit le nom de la société, soit le statut "particulier"

Dans quel champ est-ce que je fais la distinction ?

Regarde le classeur joint (si tu ne peut pas le décompresser, dis-le). Les résultats sont en colonne B, les listes en E1 (entreprises) et G1 (noms). S'il s'agît d'une entreprise, l'autre liste déroulante est supprimée. Elle est recréée s'il s'agît d'un particulier. Le nom de la base et son chemin, et le nom de la table se retrouvent plusieurs fois dans le code. Ils doivent être modifiés. Si la BDD est au format 2007, tu dois cocher la référence :
Microsoft Office nn.0 Access database engine Object Library
s'il s'agît d'une base format ".mdb", tu dois cocher la référence :
Microsoft DAO 3.n Object Library
Dans le module "ThisWorkbook", le code est le suivant :

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
Private Sub Workbook_Open()
    'cocher la référence Microsoft DAO 3.n Object Library si fichier .mdb
    'si fichier .accdb cocher la référence Microsoft Office nn.0 Access database engine Object Library
    Dim db As Database, rs As Recordset, txt1 As String, txt2 As String
    'modifier les deux lignes suivantes
    Set db = OpenDatabase("C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\colapsus.accdb")
    Set rs = db.OpenRecordset("Table1", dbOpenTable)
    'Set rs = DB.OpenRecordset("SELECT * FROM " & _
        TableName & " WHERE " & FieldName & _
        " = 'MyCriteria'", dbReadOnly) ' filter records
    i = 0
    With rs
        While Not .EOF
            txt1 = txt1 & "," & .Fields(11)
            txt2 = txt2 & "," & .Fields(2)
            .MoveNext
        Wend
    End With
    Set rs = Nothing
    db.Close
    Set db = Nothing
    txt1 = Right(txt1, Len(txt1) - 1)
    txt2 = Right(txt2, Len(txt2) - 1)
    With [Feuil1!G1].Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=txt1
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    With [Feuil1!E1].Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=txt2
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

et dans le module de Feuil1 :

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
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim db As DAO.Database
    Dim dbPath As String
    Dim rs As DAO.Recordset, NomTable As String, Champ As String
    Dim txt1 As String
 
    Application.EnableEvents = False
    If Target.Address = "$E$1" Then
        If LCase(Target.Value) <> "particulier" Then
            dbPath = "C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\colapsus.accdb"
            Set db = OpenDatabase(dbPath)
            NomTable = "Table1"  'à modifier
            Champ = "Entreprise"
            Set rs = db.OpenRecordset("SELECT * FROM " & _
                NomTable & " WHERE " & Champ & _
                " = " & "'" & Target.Value & "'", dbReadOnly) ' filter records
            With rs
                If .NoMatch Then
                    MsgBox ("Pas de correspondance")
                    Set rs = Nothing
                    db.Close
                    Set db = Nothing
                    Exit Sub
                Else
                    Sheets("Feuil1").Range("B1").Value = .Fields(4)
                    Sheets("Feuil1").Range("B2").Value = .Fields(13)
                    Sheets("Feuil1").Range("B3").Value = .Fields(15)
                    Sheets("Feuil1").Range("B4").Value = .Fields(17)
                End If
            End With
            [G1].Validation.Delete
            [G1].ClearContents
        Else
            'modifier les deux lignes suivantes
            Set db = OpenDatabase("C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\colapsus.accdb")
            NomTable = "Table1"  'à modifier
            Champ = "Entreprise"
            Set rs = db.OpenRecordset("SELECT * FROM " & _
                NomTable & " WHERE " & Champ & _
                " = " & "'particulier'", dbReadOnly)
            i = 0
            With rs
                While Not .EOF
                    txt1 = txt1 & "," & .Fields(11)
                    .MoveNext
                Wend
            End With
            Set rs = Nothing
            db.Close
            Set db = Nothing
            txt1 = Right(txt1, Len(txt1) - 1)
            With [Feuil1!G1].Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:=txt1
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            [B:B].ClearContents
        End If
    ElseIf Target.Address = "$G$1" Then
        dbPath = "C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\colapsus.accdb"
        Set db = OpenDatabase(dbPath)
        NomTable = "Table1"  'à modifier
        Champ = "Nom"
        Set rs = db.OpenRecordset("SELECT * FROM " & _
            NomTable & " WHERE " & Champ & _
            " = " & "'" & Target.Value & "'", dbReadOnly) ' filter records
        With rs
            If .NoMatch Then
                MsgBox ("Pas de correspondance")
                Set rs = Nothing
                db.Close
                Set db = Nothing
                Exit Sub
            Else
                Sheets("Feuil1").Range("B1").Value = .Fields(4)
                Sheets("Feuil1").Range("B2").Value = .Fields(13)
                Sheets("Feuil1").Range("B3").Value = .Fields(15)
                Sheets("Feuil1").Range("B4").Value = .Fields(17)
            End If
        End With
        Set rs = Nothing
        db.Close
        Set db = Nothing
    End If
    Application.EnableEvents = True
End Sub

PS. Le nom de la feuille ne peut être changé sans le changer dans le code.

J'ai supposé, peut-être à tort, que lorsqu'il s'agissait d'un particulier, il y avait la valeur "particulier dans le champ "Entreprise". Dis-moi ce qu'il en est.

[colapsus]

Bon je vais essayer d'éclaircir tout ça, pour commencer, j'ai une base de données qui a pour nom "carnet.mdb" , dans ce carnet se trouve 16 tables, et moi je travaille sur la table qui a pour nom "Contacts", dans cette table, je me concentre pour le moment sur les colonnes "nom" et "prénom", et je voudrais que ces 2 colonnes soit liées à une liste déroulante sous Excel.

Daniel, je ne suis pas sur de ce que tu a fait, pourrais tu me l'expliquer en détail ?

[Daniel.C]

D'abord, les noms de la base et de la table sont à changer. J'ai travaillé sur les champs "Nom" et "Entreprise". A l'ouverture du classeur, une liste déroulante est créée en E1 pour les entreprises et en G1 pour les noms. Lorsque tu choisis une entreprise, les champs "Adresse", "Téléphone", "Fax" et "Mail" correspondants s'affichent en B1:B4. Si "particulier" est sélectionné dans le liste en E1 (entreprises), la liste en G1 affiche les noms; tu peux ensuite choisir un nom dans la liste et les résultats s'afficheront en B1:B4. Sinon, si une entreprise est sélectionnée en E1, la liste en G1 est supprimée et les données correspondant à l'entreprise s'affichent en B1:B4.
Je ne suis pas sûr du tout qu'il y ait "particulier" dans la colonne "Entreprise" lorsqu'il ne s'agit pas d'une entreprise. Dis-moi si mon appli fonctionne correctement. De mon côté, je vais modifier le classeur pour que le nom de la base et celui de la table se trouvent en un seul endroit, afin qu'il soit facile à modifier. Si tu pouvais aussi me donner le chemin de la base ?

[colapsus]

Le chemin de l'adresse est :
C:\Users\Marc\Desktop\N'AIRSOL\Carnet.mdb

Merci de ton aide Daniel

[Daniel.C]

Regarde le classeur en pièce jointe.

[colapsus]

Ca fonctionne presque ^^, lorsque je lance la macro apres avoir modifié le code pour mettre dans le chemin ma base de données, il me demande un mot de passe, qui est malheureusement obligatoire pour acceder à carnet.mdb, ya t'il un bout de code à rajouter avec l'entete du mot de passe, ou dois je le supprimer ?

[Daniel.C]

On dirait que ma dernière réponse est passée à la trappe...

La syntaxe est la suivante :

Code :

Set db = OpenDatabase(Chemin & NomBase, False, False, "MS Access;PWD=PASSWORD")

Il faut juste remplacer "PASSWORD" par ton mot de passe. Peux-tu répercuter les modifs dans le code ou veux-tu que je te renvoie le classeur ? Comme le mot de passe est écrit en dur dans le code, il faut mettre un mot de passe pour verrouiller celui-ci, ou, sachant qu'il est relativement facile de faire sauter cette sécurité, je peux aussi demander ce mot de passe à l'ouverture du classeur et le stocker dans une variable jusqu'à la fermeture du classeur.

[colapsus]

Oui en fait, j'aimerai que tu me dises ou insérer la ligne de code qui dit lorsque je lance la macro, au moment de l'ouverture de la base de données, utilise le mot de passe indiqué, qu'il le fasse automatiquement

[Daniel.C]

Dans Module1, j'ai ajouté la ligne :

Code :

Public MotdePasse As String

Au début de la macro WorkBook_Open, j'ai ajouté :

Code :

1
2
    MotdePasse = InputBox("Entrez le mot de passe de la base", "Base Carnet.mdb")
    If MotdePasse = "" Then Exit Sub

Peux-tu seulement me donner le message d'erreur lorsque le mot de passe est incorrect.

[colapsus]

Lorsque le mot de passe est faux, on obtient un message d'erreur qui dit :

Erreur d'éxécution 3031
Erreur définie par l'application ou par l'objet

Aussi, lorsque je lance la macro, je peux bel et bien ouvrir les deux liste déroulantes, mais j'assiste a une infinité de ",,,,,,,,,,"

Puis je t'envoyer une base de données incompletes, que tu voies en détails a quoi cela ressemble ?

J'ai trouvé la réponse a cela, premierement, il fallait retrancher de un, en effet, la colonne des noms étant au 11 eme rang, il fallait mettre field(10).

Y'aurait il un moyen de faire des passages a la ligne, et de passer directement lorsque le nom n'est pas présent, au lieu d'avoir des virgules ?

Je te remercie en tout cas pour l'aide que tu m'apportes.