ListBox et fichier fermé

[Xavllo]

Bonjour à tous,

Après avoir pioché dans les différents messages du forum, j'ai réussi à faire l'exemple en pièce jointe. Cela me permet de trouver à partir du nom d'une commune son code postal et inversement à partir d'un fichier fermé contenant la base de données des communes et CP de France (ici, je l'ai limité aux 100 premières villes, pour une question de poids). Je souhaite vraiment conserver la recherche dans un fichier fermé à cause du temps d'ouverture de la base totale (plus de 36000 communes, ça pèse...)

Pour la recherche à partir du CP, impeccable.

Par contre, à partir de la recherche sur la commune, il faut saisir le nom entier de la commune pour avoir un liste de code postal. La fonction utilisée renvoie seulement les communes correspondantes exactement.
Est-il possible que la recherche dans la base de données se fasse à partir d'un nom de commune incomplet: par exemple, en tapant "ST S", il trouve toutes les villes commençant par "ST S")? La liste des villes possibles s'écrirait alors dans une première colonne d'une listbox et dans une deuxième colonne, on ait le code postal des villes de la première colonne?

Merci pour toutes vos remarques et suggestions qui me permettent de mieux comprendre VBA.

Xavier

 

[sousou]

Re : ListBox et fichier fermé

Bonjour Xavllo
La solution est peut_être celle-ci, à tester en vrai grandeur.
tu remplace ta textebox nom de la commune par une combobox;
au debut du programme, tu lance une requète qui remplie ta combobox (select lieu from bd) , ainsi tu utilise les fonction de la combox pour chercher ta commune.
Il faudrait que ton fichier code postal soit trier par lieu, ou alors tu doit trier ta combobox.

 

[MichelXld]

Re : ListBox et fichier fermé

bonjour


Tu peux tester cette adaptation dans ton userform en lieu et place de TextBox1_Exit :

Private Sub TextBox1_Change()
    Dim rs As ADODB.Recordset
 
    L_com2.Visible = True
    ListBox1.Visible = True
 
    Set cnn = New ADODB.Connection
    cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & _
        ThisWorkbook.Path & "\" & "CodePostal.xls"
    Set rs = cnn.Execute("SELECT Code FROM BD WHERE Lieu LIKE '" _
        & UCase(Me.TextBox1) & "%'")
 
    If Not rs.EOF Then Me.ListBox1.List = Application.Transpose(rs.GetRows)
 
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

bon après midi
michel
Microsoft Excel MVP

 

[sousou]

Re : ListBox et fichier fermé

Re

Voici le fichier avec ma combobox et le trie qui va bien

 

[excalibur]

Re : ListBox et fichier fermé

bonjour Xavllo sousou Michel le forum un exemple directement dans le fichier code postal a adapter

 

[Xavllo]

Re : ListBox et fichier fermé

Merci à tous les deux.

La version de Michel fonction bien mais par contre, dans la listbox, on a que les codes postaux qui s'affichent. J'aurai bien voulu avoir aussi le nom de la commune car en tapant par exemple "ST S", avec la base des 36000 communes, le nombre de réponses est très gros. C'est pour cela que je voudrais afficher dans la listbox, deux données: les villes possibles avec leur code postal.

Pour Sousou, avec ta version, on dait sélectionner dans ta combobx la ville. Mais avec 36000 communes au départ, la recherche d'un nom est super longue.


Pour Excalibur, je préfère avoir la liste des CP et communes à part. Car la liste complète pèse presque 3Mo. C'est pour cela que j'essaie de passer par la lecture dans un fichier fermé. J'avais au départ à peu près le même Userform en moins sophistiqué... De même, avec 36000 communes, le tri à chaque lettre est très long, c'est que j'avais fait au départ, pour ensuite faire le tri à la sortie de la textbox.
Merci pour l'avancement et votre rapidité

Xavier

 

[MichelXld]

Re : ListBox et fichier fermé

rebonsoir

Private Sub TextBox1_Change()
    Dim rs As ADODB.Recordset
    
    L_com2.Visible = True
    ListBox1.Visible = True
    
    Set cnn = New ADODB.Connection
    cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & _
        ThisWorkbook.Path & "\" & "CodePostal.xls"
    Set rs = cnn.Execute("SELECT Code,Lieu FROM BD WHERE Lieu LIKE '" _
        & UCase(Me.TextBox1) & "%'")
    
    If Not rs.EOF Then
        With Me.ListBox1
            .ColumnWidths = "35" 'pour la largeur de la première colonne
            .ColumnCount = rs.Fields.Count
            .List = Application.Transpose(rs.GetRows)
        End With
    End If
    
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

bonne soirée
michel
Microsoft Excel MVP

 

[Xavllo]

Re : ListBox et fichier fermé

Merci, cela fonctionne parfaitement.
Je ne savais pas que l'on pouvait mettre plusieurs arguments aprés Select. Du coup, on sélectionne directement le CP et la ville.
Pour ma culture, le mot LIKE permet comparer le début des mots dans la BD mais à quoi sert "%'".

Je pense avoir à peut près compris le reste du code.

Encore merci pour la rapidité et l'efficacité.

Xavier

 

[MichelXld]

Re : ListBox et fichier fermé

rebonsoir


% (ou * en fonction du driver de connection) est un caractère générique pour l'opérateur LIKE :





bonne soirée
michel
Microsft Excel MVP

 

[Xavllo]

Re : ListBox et fichier fermé

Bonjour,

Je reviens sur la lecture dans un fichier fermé.
Première question: rs.Fields.Count permet de savoir le nombre de "colonnes" de la recherche. Et pour le nombre de lignes?
Deuxième question: après SELECT, existe-t-il un caractère générique pour sélectionner tous les champs de la BD ou faut-il les écrire un par un et quel est le nombre maximum de champs que l'on peut extraire?

Merci pour votre temps passé à solutionner le problème des novices.

Xavier

 

[MichelXld]

Re : ListBox et fichier fermé

bonsoir

Et pour le nombre de lignes?

MsgBox rs.RecordCount

existe-t-il un caractère générique pour sélectionner tous les champs de la BD

C'est l'astérisque :

SELECT * FROM BD

La syntaxe complète pour ton classeur :

Private Sub TextBox1_Change()
    Dim rs As ADODB.Recordset
    
    L_com2.Visible = True
    ListBox1.Visible = True
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
        "data source=" & ThisWorkbook.Path & _
        "\" & "CodePostal.xls;" & _
        "extended properties=""Excel 8.0;"""
    
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM BD WHERE Lieu LIKE '" & _
        UCase(Me.TextBox1) & "%';", cnn, adOpenKeyset
    If Not rs.EOF Then
        MsgBox rs.RecordCount
        With Me.ListBox1
            'pour la largeur de la première colonne
            .ColumnWidths = "35"
            .ColumnCount = rs.Fields.Count
            .List = Application.Transpose(rs.GetRows)
        End With
    End If
    
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

bonne soirée
michel
Microsoft Excel MVP

 

[Xavllo]

Re : ListBox et fichier fermé

Une dernière petite question: comment faire pour récupérer les données directement dans des cellules sans passer par la listbox?
Je ne suis plus avec les CP et les communes mais avec une série de données en colonnes.
J'ai essayé un truc du genre :
Sheets("Feuil1").Range("A1:" & Chr(64 + rs.Fields.Count) & rs.RecordCount -1) = Application.Transpose(rs.GetRows)
mais ça bloque. La zone a pourtant la bonne taille puisqu' à la place de Application.Transpose(rs.GetRows) j'écris listbox1.list, j'ai bien la liste en fonction des critères de recherche.

Merci encore pour les conseils précédents.

Xavier

 

[MichelXld]

Re : ListBox et fichier fermé

Rebonsoir,


Il existe un méthode intégrée pour écrire directement le résultat de la requête dans la feuille de calcul : CopyFromRecordsert

Private Sub TextBox1_Change()
    Dim rs As ADODB.Recordset
    
    Cells.Clear
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
        "data source=" & ThisWorkbook.Path & _
        "\" & "CodePostal.xls;" & _
        "extended properties=""Excel 8.0;"""
    
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM BD WHERE Lieu LIKE '" & _
        UCase(Me.TextBox1) & "%';", cnn, adOpenKeyset
    
    If Not rs.EOF Then Range("A1").CopyFromRecordset rs
    
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

bonne soirée
michel
Microsoft Excel MVP

 

[Xavllo]

Re : ListBox et fichier fermé

Rererebonsoir,

Merci comme toujours, cela fonctionne parfaitement! Comment trouves-tu toutes ces solutions? L'expérience? J'ai cherché des infos avec le lien qui tu m'as donné mais je n'ai rien trouvé qui puisse m'aider à résoudre mon problème ou alors je n'arrive pas à chercher au bon endroit...

En tout cas, la solution de recherche via un fichier fermé est très rapide dans mon cas. Tant que j'y suis, j'ose une dernière série de questions: j'ai un fichier appelé BaseMDS.xls qui me sert de fichier ressource pour stocker des infos sur des maîtres de stage pour mes élèves de toutes les filières.
Dans ma première version de fichier de gestion, pour extraire les données d'une filière, je passe par des boucles FOR balayant tout le fichier pour copier les maîtres de stage de la filière dans une feuille du fichier principal, afin de gagner en confort d’utilisation car ma copie nécessite l'ouverture du fichier BaseMDS.
Au moment d'enregistrer, je réouvre le fichier BaseMDS, supprime les infos de la filière puis recopie les données modifiées. Fonctionnel mais long en tant de chargement. C'est pour cela que je tente l'aventure des fichiers fermés.
Pour l'extraction, pas de problème avec le code de Michel. Mais la sauvegarde? Est-il possible de supprimer l'enregistrement des infos de la filière dans le fichier BaseMDS puis de recopier les nouvelles infos du fichier principal vers le fichier BaseMDS (tout en le laissant fermé…).
Pour clarifier la situation, je vous joints 2 fichiers : le fichier BaseMDS avec plusieurs maîtres de stage dans chaque filière. Un fichier TestMDS, raccourci de mon gros fichier avec seulement la feuille 1 qui sert de menu et la feuille MDS qui sert de stockage temporaire des informations sur les maitres de stages de la filière sélectionnée.

J'espère que je suis suffisamment clair dans mon explication, mais ce n'est pas facile d'expliquer simplement lorsque cela fait longtemps que l'on travaille sur un projet.

Merci pour vos éclairages et vos conseils.

Xavier

 

[Xavllo]

Re : ListBox et fichier fermé

Bonsoir,

Avant d'écrire dans mon fichier fermé, je souhaite d'abord supprimer des informations. Pour supprimer, j'ai essayé le code suivant:

Sub suppression()
Dim Cn As ADODB.Connection

Set Cn = New ADODB.Connection
Cn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"data source=" & ThisWorkbook.Path & _
"\" & "Base MDS.xls;" & _
"extended properties=""Excel 8.0;"""
Cn.Execute "DELETE * FROM BD WHERE tri=" & UCase(Sheets("Feuil1").Cells(1, 1))

Cn.Close
Set Cn = Nothing
End Sub

Mais il me retourne l'erreur suivante: Aucune valeur donnée pour un ou plusieurs des paramètres requis.

Quelqu'un pourrait-il m'éclairer de ses lumières car là, je séche...

Merci d'avance

Xavier