Recherche dans liste externe

erics83 · 26 Août 2015

Bonjour,

Je cherche à faire un Userform qui recherche des noms dans un classeur fermé, la recherche se faisant en tapant les premières lettres du nom.

J'ai essayé le code conseillé par Jacques Boisgontier (qui donne un lien vers ce site et dont les tutos sont super !), c'est le code

Code:

Dim Liste
Private Sub UserForm_Initialize()
  'Microsoft ActiveX DataObject doit être coché
  ' Champ nommé BD
  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & _
     ThisWorkbook.Path & "\" & "BDPROD.xls"
  Set rs = cnn.Execute("SELECT libellé FROM BD")
  Me.ListBox1.List = Application.Transpose(rs.GetRows)
  Liste = Me.ListBox1.List
  rs.Close
  cnn.Close
  Set rs = Nothing
  Set cnn = Nothing
End Sub

Private Sub TextBox1_Change()
  Me.ListBox1.Clear
  For Each c In Liste
    If UCase(c) Like UCase(Me.TextBox1) & "*" Then Me.ListBox1.AddItem c
  Next c
End Sub

Private Sub TextBox2_Change()
   Me.ListBox1.Clear
   For Each c In Liste
      If UCase(c) Like "*" & UCase(Me.TextBox2) & "*" Then Me.ListBox1.AddItem c
   Next c
End Sub

Private Sub ListBox1_Click()
   ActiveCell = Me.ListBox1
   Unload Me
End Sub

mais je n'arrive pas à le faire fonctionner, il bloque au niveau de

Code:

 Me.ListBox1.List = Application.Transpose(rs.GetRows)

et je ne comprends pas pourquoi....je suis sous excel 2003...

En vous remerciant pour votre aide,

erics83 · 1 Septembre 2015

Re : Recherche dans liste externe

Super merci BOISGONTIER,

Je vois où j'avais fait ma principale erreur : c'était dans ma déclaration :
Dim c (1 to 1, 1 to 3)

Merci,
Et pour le if VAL (Application.Version)>10 then SendKeys "{F4}", une dernière explication de texte ? Si vous avez 2-3 minutes bien évidemment.....

En vous remerciant,
Merci,

erics83 · 1 Septembre 2015

Re : Recherche dans liste externe

Merci BOISGONTIER,

Je continue de comprendre et d'essayer vos codes, qui m'ont donné quelques idées...

Pour revenir au thème du post, et de la lecture de liste externe, j'ai repris votre exemple du classeur LISTDEROULzz, dans mon classeur BDPROD, j'ai des données jusqu'à la colonne Z : Référence, Nom, Prénom, Adresse, Téléphone, Ville, mail, etc....

Faut-il que j'écrive :

Code:

Set rs = cnn.Execute("SELECT Référence, Nom, Prénom, Adresse, Téléphone, Ville, mail, etc....

J'ai fait le test, mais je trouve que cela ralenti un peu le code, alors que vous m'avez donné une super modification qui effectivement accélère le code...

N'y a-t-il pas un autre moyen ? = une fois que ma valeur est trouvée (=ListBox1), que le code aille chercher la ligne correspondante dans BDPROD et rapatrie/recopie toute la ligne dans la Feuil1 (par exemple) en A1, B1, C1, etc....Cela permettrait par exemple, de mettre à profit un de vos codes (ajout/modification d'un enregistrement)

En vous remerciant pour votre aide,

stefyu89 · 1 Septembre 2015

Re : Recherche dans liste externe

@ Jacques Boisgontier

Vous êtes le meilleur !!! le MEILLEURRRRRRRRRRRRRRRRRRRRRRRR en VBA , liste par cascades et données de validation !!! Les fichiers sont un plaisir à analyser, à triturer et à réanalyser !!! Je suis à court de superlatifs !!!

Juste un petit bémol : pas d'OPTION EXPLICIT !!! Mais bon juste pour chipoter !!! Du moment où le code est net et pur et que les machines aujourd'hui sont suffisamment performantes pour gérer le changement de type sans problème , c'est vraiment chipoter que d'exigencer OPTION EXPLICIT même si ... Je crois que je vais arrêter là.

Merci encore pour tout !!! Vous êtes un exemple de générosité et de partage de la connaissance. A quand un livre sur VBA et l'analyse de données : les livres sur Pascal et d'autres langages d'un autre âge datent véritablement ... (je taquine encore)

Stephane NTONGA

erics83 · 1 Septembre 2015

Re : Recherche dans liste externe

Surtout que j’ai des problèmes avec mes champs : il y a des espaces, et la connexion ADO n'aime pas trop cela....exemple "code postal", la connexion bloqué, et lorsque je mets "codepostal" cela fonctionne...mais je n'ai "pas la main" sur les noms des champs de la BDPROD....comme je l'indiquais plus haut, BDPROD est écrasée chaque semaine, et donc, même si je modifie les noms des champs (exemple code postal en codepostal), chaque semaine, il faudrait que je rechange tous les noms des champs....C’est pourquoi, je cherche le numéro de ligne....

Plus simple à mes yeux...

Merci pour votre aide,

et concernant les louanges de stefyu89 pour BOISGONTIER, entièrement d'accord !!! JB est une mine d'informations et de solutions !!!! s'il sort un livre et/ou compléments, j'achète !!!

BOISGONTIER · 1 Septembre 2015

Re : Recherche dans liste externe

>Surtout que j’ai des problèmes avec mes champs : il y a des espaces, et la connexion ADO n'aime pas trop cela....exemple "code postal", la connexion bloqué, et lorsque je mets "codepostal" cela fonctionne...mais je n'ai "pas la main" sur les noms des champs de la BDPROD..

En écrivant les noms de champ entre [] (Select ...,[Code postal],..), les requêtes SQL devraient fonctionner.

Ce n'est pas la requête externe SELECT qui doit ralentir le pgm mais l'alimentation de la Listbox avec :

Code:

    ListBox1.AddItem rs("libellé")
    ListBox1.List(i, 1) = rs("codification")
    ListBox1.List(i, 2) = rs("Prix")
    ListBox1.List(i, 3) = rs("Unité")

Il est préférable d'alimenter une table Liste(,) avec :

Code:

    Liste(i, 1) = rs("libellé")
    Liste(i, 2) = rs("codification")
    Liste(i, 3) = rs("Prix")
    Liste(i, 4) = rs("Unité")

et de transférer la table dans le ListBox avec

ListBox1.List=Liste

JB

erics83 · 1 Septembre 2015

Re : Recherche dans liste externe

Ok Merci BOISGONTIER, j'essaye tout de suite les [], je vais les mettre pour tous les champs....et faire la motif de la Listbox1

Par contre, c'est la seule solution ? on est obligé de tout charger ? car vous l'avez compris, la recherche est uniquement importante pour les 3 premières colonnes...Pour rapatrier les données, j'avais fait

Code:

For j =3 to 24
sheets("Feuil1").cells(1,2).offset(j,0)= Me.Combobox1.column(j)
Next

Cela me permet de récupérer les données dans 1 seule colonne.

Mais je me demandais s'il n'était pas possible de récupérer toutes les données, sans passer par les noms des champs (hormis les 3 premiers...) car il n'est pas impossible que dans le temps, le nom des champs de la BDPROD évoluent et changent (ils ne sont pas à ma main), par exemple Code postal en CP, ou autres, et donc il faudrait que je change mon code à chaque fois (ce n'est pas fréquent, mais....), d'où l'idée de rapatrier la ligne : le champ "libellé" ou "Nom" étant les champs qui ne changeront jamais....

En vous remerciant,

BOISGONTIER · 1 Septembre 2015

Re : Recherche dans liste externe

-set rs = cnn.Execute("SELECT * FROM Feuil1$A1:Z1000") récupère tous les champs de la BD

-Les champs peuvent être adressés avec rs(1),rs(2),.....

- On peut transférer tout le résultat de la requête dans une feuille avec

Sheets("Temp").[A2].CopyFromRecordset rs

JB

erics83 · 1 Septembre 2015

Re : Recherche dans liste externe

Merci BOISGONTIER,

Mais comme je le disais, je ne peux créer un BD, puisque ma BDPROD est écrasée toutes les semaines, d'où l'utilité du

Code:

FROM [TABLE$A1:D1000]

que vous m'avez conseillé....

Nos messages se sont croisés...donc vous en concluez, qu'il vaut mieux que je rapatrie l'ensemble de BDPROD, via Sheets("Temp").[A2].CopyFromRecordset rs, cela revient à ce que je pensais à la base : rapatrier tous le BDPROD....c'est dommage car votre code, est vraiment magnifique...surtout pour un moyen-débutant comme moi.... car il permettait de lire dans le classeur fermé et de faire des requetages dessus, dommage qu'on ne puisse pas récupérer cette fameuse ligne que l'on aura sélectionné dans ListBox1....Grrrrr

Merci pour votre aide,

BOISGONTIER · 1 Septembre 2015

Re : Recherche dans liste externe

>dommage qu'on ne puisse pas récupérer cette fameuse ligne que l'on aura sélectionné dans ListBox1....Grrrrr

Code:

  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & _
      ThisWorkbook.Path & "\" & "BDPROD.xls"
  Set rs = cnn.Execute("SELECT * FROM [TABLE$A1:D1000] where libellé='" & Me.ListBox1 & "'")
  [j2].CopyFromRecordset rs

jb

erics83 · 1 Septembre 2015

Re : Recherche dans liste externe

Merci JB (si je puis me permettre...)

J'ai essayé, mais j'ai un problème au niveau de l'empale du code que vous m'avez conseillé : si je mets Me.Listbox1 au début du code, le code ne "connait" pas encore la valeur de listbox1...

je mets le fichier en PJ + le code :

Code:

Dim Liste()
Private Sub UserForm_Initialize()
  'Microsoft ActiveX DataObject doit être coché
  ' Champ nommé BD
 Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & _
      ThisWorkbook.Path & "\" & "BDPROD.xls"
  Set rs = cnn.Execute("SELECT count(*) as nb FROM [TABLE$A1:D1000] where libellé<>''")
  ReDim Liste(0 To rs("nb"), 1 To 4)
  'Set rs = cnn.Execute("SELECT libellé,Codification,Prix,Unité FROM BD where libellé<>''")
  'Set rs = cnn.Execute("SELECT libellé,Codification,Prix,Unité FROM [TABLE$A1:D1000] where libellé<>''")
  
    Set rs = cnn.Execute("SELECT * FROM [TABLE$A1:D1000] where libellé='" & Me.ListBox1 & "'")
  [j2].CopyFromRecordset rs

  
  
  Me.ListBox1.Clear
  i = 0
  Do While Not rs.EOF
    On Error Resume Next   ' cellules vides
    Liste(i, 1) = rs("libellé")
    Liste(i, 2) = rs("codification")
    Liste(i, 3) = rs("Prix")
    Liste(i, 4) = rs("Unité")
    On Error GoTo 0
    i = i + 1
    rs.MoveNext
  Loop
  Me.ListBox1.List = Liste
  rs.Close
  cnn.Close
  Set rs = Nothing
  Set cnn = Nothing
  Liste = Me.ListBox1.List
End Sub
Private Sub TextBox1_Change()
   Me.ListBox1.Clear
   j = 0
   For i = LBound(Liste) To UBound(Liste)
     If UCase(Liste(i, 0)) Like "*" & UCase(Me.TextBox1) & "*" _
        Or "*" & UCase(Liste(i, 1)) Like "*" & UCase(Me.TextBox1) & "*" Then
        On Error Resume Next
        Me.ListBox1.AddItem Liste(i, 0)
        Me.ListBox1.List(j, 1) = Liste(i, 1)
        Me.ListBox1.List(j, 2) = Liste(i, 2)
        Me.ListBox1.List(j, 3) = Liste(i, 3)
        On Error GoTo 0
        j = j + 1
     End If
   Next i
End Sub

Private Sub ListBox1_Click()
  ActiveCell = Me.ListBox1
  ActiveCell.Offset(, 1) = Me.ListBox1.Column(1)
  ActiveCell.Offset(, 2) = CDbl(Me.ListBox1.Column(2))
  ActiveCell.Offset(, 3) = Me.ListBox1.Column(3)
  Unload Me
End Sub

Je dois faire une erreur, mais je ne vois pas à quel niveau....à moins que vous me conseilliez de refaire une connexion ADO qui "relancerait" le code avec

Code:

Set rs = cnn.Execute("SELECT * FROM [TABLE$A1:D1000] where libellé='" & Me.ListBox1 & "'")

mais je ne vois pas à quel niveau il faudrait que je le place....

En vous remerciant,

BOISGONTIER · 1 Septembre 2015

Re : Recherche dans liste externe

Voir PJ

JB

erics83 · 2 Septembre 2015

Re : Recherche dans liste externe

Super Merci JB,

C'était bien ce que je pensais...2 connexions : 1 pour faire la recherche, 1 pour rapatrier les données...mais je ne savais pas où mettre la 2ème connexion...Super Merci !!!

La toute dernière question : J'ai un problème de largeur dans la ListBox

dans votre BDPROD, la première colonne sont des noms, la deuxième des référence.

A l'affichage de votre ListBox, les colonnes s'affichent bien.

Par contre, dans mon BDPROD, dans la première colonne j'ai des références, et dans la deuxième des Noms. Il y a un problème de largeur car beaucoup d'espace pour la première colonne, et la deuxième est tronquée (=on ne voit pas tout le nom).

Je n'ai pas trouvé d'instruction spécifique dans votre code pour définir la largeur des colonnes dans la ListBox, j'ai essayé d'agrandir mes colonnes dans BDPROD, cela n'a pas fonctionné...

Une dernière petite aide ?

En vous remerciant,

BOISGONTIER · 2 Septembre 2015

Re : Recherche dans liste externe

Bonjour,

C'est la propriété ColumnWidth du ListBox

JB

erics83 · 2 Septembre 2015

Re : Recherche dans liste externe

Parfait !!! Merci JB, je ne connais pas cette possibilité, merci, j’ai modifié, tout est nickel.

Merci pour votre aide tout au long ce ce post, et aussi pour tous vos tutos (je me répète, mais ils m'ont tellement aidé...)

Merci, et à bientôt pour de prochaines aventures,

erics83 · 2 Septembre 2015

Re : Recherche dans liste externe

....erreur message....

Recherche dans liste externe

erics83

XLDnaute Impliqué

erics83

XLDnaute Impliqué

erics83

XLDnaute Impliqué

stefyu89

XLDnaute Nouveau

erics83

XLDnaute Impliqué

BOISGONTIER

XLDnaute Barbatruc

erics83

XLDnaute Impliqué

BOISGONTIER

XLDnaute Barbatruc

erics83

XLDnaute Impliqué

BOISGONTIER

XLDnaute Barbatruc

erics83

XLDnaute Impliqué

Pièces jointes

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

erics83

XLDnaute Impliqué

BOISGONTIER

XLDnaute Barbatruc

erics83

XLDnaute Impliqué

erics83

XLDnaute Impliqué

Discussions similaires