Créer un annuaire Excel : rechercher dans les contacts en VBA et MYSQL

Posted juillet 28th, 2008 by bout2code

Cet article fait suite à l’article : Créer un annuaire en excel avec une base de donnée SQL. Vous pouvez retrouver les explications de cette création d’annuaire sur celui-ci ainsi que le sommaire.

Étape 3 : rechercher dans l’annuaire excel

Dans la première partie de l’explication, nous avons préparé notre fichier Excel, avec des macros VBA de recherche qui nous permettaient de rechercher avec chacune des lettres de l’alphabet. Nous allons maintenant lier ces macros avec une recherche SQL pour ressortir les personnes inscrites dans l’annuaire.

On va aussi rajouter une fonction de trie par défaut pour l’affichage des contacts.

Annuaire

  • Macro par lettre

Avec cette macro, on va définir ce que l’on recherche, de manière à indiquer à la requête SQL ce qu’elle doit rechercher. On va ainsi réduire le nombre de macro dans notre fichier, et ainsi économiser son temps passé dessus !

Donc pour chaque lettre, on va créer une macro spécifique :

  1.  
  2.  
  3. Sub lancementrechercheA()
  4.  
  5. Dim lettrestart As Variant
  6.  
  7. ‘on indique quelle lettre on recherche
  8. lettrestart = a
  9.  
  10. ‘on vient la mettre dans une feuille caché
  11. Sheets("liaison").Visible = True
  12. Sheets("liaison").Select
  13.    
  14.     Range("D29").Select
  15.     ActiveCell.FormulaR1C1 = "a"
  16.    
  17. ActiveWindow.SelectedSheets.Visible = False
  18.  
  19. ‘et on lance la macro de recherche dans l’annuaire !
  20. Application.Run "top_affiliés.xlsm!rechercheannuaire"
  21.  
  22. End Sub
  23.  

Cette macro va nous permettre de définir une recherche par lettre. Cette macro doit être dupliqué pour chacune des lettres de l’alphabet !

Macro de Recherche

    • On va ensuite écrire la macro de recherche dans la BDD sql, sous cette forme :

      1.  
      2. Sub rechercheannuaire()
      3.  
      4. ‘on récup la bdd
      5.  
      6. Sheets("BDD").Visible = True
      7. Sheets("BDD").Select
      8. Dim server, uid, pwd, database As Variant
      9.  
      10. server = Range("E8")
      11. uid = Range("E10")
      12. pwd = Range("E12")
      13. database = Range("E14")
      14. ActiveWindow.SelectedSheets.Visible = False
      15.  
      16. ‘on récup les valeurs
      17.  
      18. Dim nbdata, trie, trieR, lettrestart As Variant
      19.  
      20. Sheets("liaison").Visible = True
      21. Sheets("liaison").Select
      22.  
      23. nbdata = Range("D27")
      24. trie = Range("B26")
      25. trieR = Range("D26")
      26. lettrestart = Range("D29")
      27.  
      28. ActiveWindow.SelectedSheets.Visible = False
      29.  
      30. ‘on vide les data si il y en a !
      31.  
      32. If nbdata > 6 Then
      33. Sheets("annuaire").Select
      34. Range("G7:O" & nbdata).Select
      35. Selection.ClearContents
      36.  
      37. End If
      38.  
      39. ‘ on vient indiquer que l’on fait une recherche ! ce qui nous permet par la suite d’éditer de supprimer des contacts
      40.  
      41. Sheets("annuaire").Select
      42. Range("A1").Select
      43. ActiveCell.FormulaR1C1 = "1"
      44.  
      45. ‘on choisit le trie
      46. If (trie = 1) Then
      47. trieR = "nom"
      48. End If
      49.  
      50.  
      51. ‘on déclare les variables de la BDD
      52.  
      53. ‘Déclaration des variables BDD
      54. Dim cnx As ADODB.Connection
      55. Dim rst As ADODB.Recordset
      56.  
      57. ‘Instanciation des variables
      58. Set cnx = New ADODB.Connection
      59. Set rst = New ADODB.Recordset
      60.  
      61. ‘Définition de la chaîne de connexion
      62.  
      63. connstring = "driver={MySQL ODBC 3.51 Driver}; server=" & server & ";uid=" & uid & "; pwd=" & pwd & ";database=" & database
      64. cnx.Open connstring
      65.  
      66. ‘on va ici rechercher tous les noms qui commencent par la lettre que l’on a définit plus tôt !
      67. rst.Open "SELECT nom, prenom, fonction, mail, tel1, tel2, adresse FROM annuaire WHERE nom like ‘" & lettrestart & "%’ ORDER BY " & trieR, cnx
      68.  
      69. ‘on choisit la ligne de départ pour coller les valeurs !              
      70. b = 7
      71.  
      72. ‘ici on dit : "si la requete retourne bien quelque chose, copie les valeurs comme ceci "
      73. If Not rst.EOF Then
      74. Do While Not rst.EOF
      75.  
      76. With Sheets("annuaire")
      77.  
      78. ‘ici on selectionne la ligne et la colonne avec la fonction cells(ligne,colonne)
      79. Cells(b, 8).Select
      80. ActiveCell.Formula = rst(0) ‘rst(0) correspond au premier enregistrement de la requete…
      81. Cells(b, 9).Select
      82. ActiveCell.Formula = rst(1)
      83. Cells(b, 10).Select
      84. ActiveCell.Formula = rst(2)
      85. Cells(b, 11).Select
      86. ActiveCell.Formula = rst(3)
      87. Cells(b, 12).Select
      88. ActiveCell.Formula = rst(4)
      89. Cells(b, 13).Select
      90. ActiveCell.Formula = rst(5)
      91. Cells(b, 14).Select
      92. ActiveCell.Formula = rst(6)
      93.  
      94. ‘on incrémente le b pour passer à la ligne suivante
      95. b = b + 1
      96.  
      97. End With
      98. rst.MoveNext ‘– Passage à l’enregistrement suivant
      99. Loop
      100.  
      101. Range("E5").Select
      102.  
      103. Else
      104.  
      105. ’si on a rien dans la bdd, on affiche le message suivant !
      106. MsgBox "la recherche avec la lettre : " & lettrestart & " n’a retourné aucun résultat "
      107.  
      108. End If
      109.  
      110. End Sub
      111.  

      Avec cette fonction, on va retourner les enregistrements directement dans notre tableau depuis la base de données. L’avantage de passer dans la base de données, c’est que le temps d’exécution de cette requête est très rapide, à la différence des requêtes de recherche d’Excel en général.

      On peut aussi adapter la fonction ci-dessus pour afficher l’ensemble des contacts en modifiant la requete SQL ce qui donne :

      1.  
      2. rst.Open "SELECT nom, prenom, URL, fonction, mail, tel1, tel2, adresse FROM annuaire ORDER BY " & trieR, cnx
      3.  

      ou encore rechercher les contacts qui ont des chiffres dans leur nom ! (ça peut arriver !)

      1.  
      2. rst.Open "SELECT nom, prenom, URL, fonction, mail, tel1, tel2, adresse FROM annuaire WHERE nom like ‘0%’ OR nom like ‘1%’ OR nom like ‘2%’ OR nom like ‘3%’ OR nom like ‘4%’ OR nom like ‘5%’ OR nom like ‘6%’ OR nom like ‘7%’ OR nom like ‘8%’ OR nom like ‘9%’ ORDER BY " & trieR, cnx
      3.  

      Voilà un peut pour la recherche, maintenant on va pouvoir passer à l’édition des contacts ; le rajout, la suppression.

      Tags: , , , , ,   Posted in excel, vba

    • Créer un annuaire en excel avec une base de donnée SQL
    • Créer un annuaire Excel : la base de données (MYSQL)
    • Réaliser et comprendre les sélections en VBA !
    • Passer en mode DEVELOPPEUR sur Excel !
    • Excel : Évitez les crises d’épilepsie !

    • Laisser un commentaire

    Votre commentaire :