Lister les onglets à partir d'un classeur sélectionné

Salut , je suis nouvelle , je viens de m'inscrire après plusieurs heures de recherche je reviens vers vous .

(pour l'intitulé je ne savais pas quoi mettre exactement )

Je travail sur un projet : Recherche Multi-Critères sur une feuille qui se trouve dans plusieurs classeur sous le même nom , y'a que les chiffres qui changent mais pas la structure du tableau ni les entêtes/titres .

Pour l'instant , ce que j'ai pu avoir comme idée : lister les fichiers dans un LISTBOX1 puis une fois le classeur est sélectionné, un autre LISTBOX2 aura les noms de toutes les feuilles du classeur, pour que l'utilisateur puisse choisir la feuille concernée ET ENSUITE effectuer la recherche Multi-Critères sur cette feuille ( les autres comboBox se remplissent avec les entêtes du tableau de la feuille choisi = critères ) .

Je peux lister les fichiers dans LISTBOX1 , maintenant , comment puis je remplir LITBOX2 avec les onglets du classeur sélectionné dans LISTBOX1?

J'ai pensée à ce code : ( je suis novice en VBA) :

Option Explicit

Private WB As Workbook
Dim f As Worksheet

Private Sub ListBox1_Click()

ListBox2.Clear

If ListBox1.ListIndex <> "" Then

  For Each f In WB.Worksheets
        ListBox2.AddItem f.Name
    Next f
End If
End Sub

j'ai une erreur : j'ai une erreur variable objet ou variable bloc with non défini sur la ligne :

  For Each f In WB.Worksheets

il me semble qu'il faut activer le WROKBOOK sélectionné pour qu'on puisse lire dedans afin de pourvoir lister les onglets? non ? des aides SVP merci d'avance

Bientot le week-end

bonjour peerl le forum

Tu veux donc faire une recherche sur plusieurs fichiers, dans une feuille qui elle a le même nom c'est bien cela?

si c'est le cas tu mets déjà tous tes fichiers dans un même dossier et ton fichier recherche dedans aussi et tu nous redis.

au besoin tu nous zip le dossier avec 2 ou trois fichier seulement et pas de données confidentielles dedans et tu le joins a ton post

dans l'attente de te lire

a+

Papou

Bonjour,

Voici un code à mettre dans le module de ton formulaire (UserForm) !

Poses deux ListBox sur ton formulaire (laisser les noms par défaut ListBox1 et ListBox2), au lancement de l'UserForm, une boite de message demande si on veux faire le choix d'un dossier si oui, une boite de dialogue s'ouvre demandant de choisir le dossier où se trouve les fichiers visés, une fois le choix fait, la liste est chargée dans la ListBox1 et au clic sur un des fichiers dans cette dernière, les noms des feuilles qu'il possède apparaissent dans la ListBox2 :

Dim Dossier As String
Dim Existe As Boolean

Private Sub UserForm_Activate()

    Dim Tbl() As String

    If MsgBox("Voulez-vous faire le choix du dossier où se trouvent les classeurs ?", _
              vbQuestion + vbYesNo, _
              "Choix du dossier") = vbNo Then Exit Sub

    With Application.FileDialog(4)
        If .Show = -1 Then Dossier = .SelectedItems(1)
    End With

    If Dossier = "" Then Exit Sub

    Dossier = Dossier & "\"

    'appel de la fonction...
    Tbl() = RecupFichiers(Dossier)

    'si le tableau a été initialisé...
    If Not Not Tbl Then

        Existe = True
        ListBox1.List = Tbl()

    End If

End Sub

Private Sub ListBox1_Click()

    Dim Cat As Object
    Dim Tbl() As String

    If Existe = False Then Exit Sub

    'crée l'objet catalogue
    Set Cat = CreateObject("ADOX.Catalog")

    Cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                           Dossier & ListBox1.Text & _
                           ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=2;"""

    'récupère les noms des feuilles
    Tbl = FeuillesExcel(Cat)

    'rempli la liste
    ListBox2.List = Tbl()

End Sub

Private Sub ListBox2_Click()

    MsgBox "En attendant plus d'explications, la feuille sélectionnée est : " & ListBox1.Text

End Sub

'************************
'* PROCEDURES/FONCTIONS *
'************************
Function RecupFichiers(Chemin As String) As String()

    Dim TblFichiers() As String
    Dim Fichier As String
    Dim I As Integer

    Fichier = Dir(Chemin & "*.xls*") 'si seuls les.xlsx sont voulus,mettre "*.xlsx"

    Do While (Len(Fichier) > 0)

        I = I + 1: ReDim Preserve TblFichiers(1 To I)
        TblFichiers(I) = Fichier

        Fichier = Dir()

    Loop

    RecupFichiers = TblFichiers()

End Function

Public Function FeuillesExcel(Cat As Object) As String()

    Dim Tbl As Object
    Dim TblFeuilles() As String
    Dim I As Integer

    'crée l'objet table
    Set Tbl = CreateObject("ADOX.Table")

    'passe les feuilles en revue et récupère leurs nom sans le dollar
    'dans un tableau
    For Each Tbl In Cat.Tables

        I = I + 1: ReDim Preserve TblFeuilles(1 To I)
        TblFeuilles(I) = Replace(Tbl.Name, "$", "")

    Next

    'passe à la fonction
    FeuillesExcel = TblFeuilles

    Set Tbl = Nothing

End Function

Salut, je m'excuse pour la réponse tardive suite à des raisons personnelles

le problème est résolu merci THEZE .

Je joins le dossier ( un exemple pas de données confidentiels ) comme PARITEC me l'a demandé .

Je pense ouvrir une autre discussion avec un autre intitulé pour la suite ? car je bloque sur : comment lire dans les onglets ?

Le but c'est de pouvoir chercher dans cet onglet et donc j'ai pensé à ce formulaire .

La question que je me pose quelle est la fonction qui me permet de lire dans les feuilles ( ou la feuille précisément ) afin de pouvoir récupérer les données en rouge en fonction des titres des deux tables + les ids ( les numéros en vert + libelle ) et les entêtes des colonnes (ici dans cet exemple [C:F]).

Merci infiniment

Bon début de semaine

Bonjour,

Une fois le choix du classeur et de la feuille réalisé, il suffit d'effectuer une recherche avec Find() ou "Like" dans la plage utilisée de la feuille. Pour ça, je te poste une fonction qui définie la plage à partir de A1 jusqu'à la dernière cellule utilisée (cellule à l'intersection de la plus en bas et la plus à droite). Fonction à rajouter aux deux autres en bas de page :

Function DefPlage(Fe As Worksheet, Optional L As Long = 1, Optional C As Long = 1) As Range

    On Error GoTo Fin

    With Fe

        Set DefPlage = .Range(.Cells(L, C), _
                       .Cells(.Cells.Find("*", .[A1], -4123, , _
                       1, 2).Row, .Cells.Find("*", .[A1], -4123, , _
                       2, 2).Column))

    End With

    Exit Function

Fin:

    Set DefPlage = Nothing

End Function

Ici, le début de code pour effectuer la recherche de valeur dans la feuille sur Click() de la ListBox2.

Comme je ne sais pas vraiment ce que tu veux faire, je ne peux pas te poster un code prêt à l'emploi donc, en attendant plus d'explications voici déjà :

Private Sub ListBox2_Click()

    Dim Cls As Workbook
    Dim Fe As Worksheet
    Dim Plage As Range
    Dim Cel As Range
    Dim Critere As String

    'dans le cas où il serait déjà ouvert, erreur si ce n'est pas le cas donc la gérer
    On Error Resume Next
    Set Cls = Workbooks(ListBox1.Text)

    'si il y a erreur, le classeur n'est pas ouvert donc, ouverture
    If Err.Number <> 0 Then Set Cls = Workbooks.Open(Dossier & ListBox1.Text)

    'fin du gestionnaire
    On Error GoTo 0

    'gèle le raffraîchissement de l'écran
    Application.ScreenUpdating = False

    Set Fe = Cls.Worksheets(ListBox2.Text)

    Critere = "Que cherches-tu ?"

    Set Plage = DefPlage(Fe, 1, 1)

    If Plage Is Nothing Then MsgBox "Aucune données sur la feuille '" & Fe.Name & "' !"

    Set Cel = Plage.Find(Critere, , xlValues, xlWhole)

    If Cel Is Nothing Then MsgBox "Le critère de recherche '" & Critere & "' est introuvable sur la feuille '" & Fe.Name & "' !": Exit Sub

    'ici le code de récupération si trouvé !
    '...
    '...
    '...

    'fermeture une fois récup réalisée !
    Cls.Close False

    Application.ScreenUpdating = True

End Sub

Bonsoir ,

Merci pour ce code je le test et je vous tiens au courant des résultats .

Pour plus d'explication : je voudrais pouvoir utiliser ce formulaire afin de pouvoir récupérer les données que j'ai coloré en rouge dans les tableau : en gros j'ai X fichiers ( un fichier représente un établissement ) avec X onglets pour chaque fichiers . Le but de lister les fichiers et leurs onglets dans LISTBOX2 c'est de pouvoir choisir directement la feuille ou je dois effectuer ma recherche nommé par exemple : K_2_onglet dans certains fichiers on peut trouver la même feuille nommé juste K_onglet mais la structure des tableaux à l'intérieur ne changent pas reste la même avec les mêmes entêtes .

dans le formulaire ( c'est juste une idée si y'a meilleur je ne dirais pas non ) on a :

ComboBox "titre tab 1" --> qui récupérè les titres de la table 1 ( titre 1 : AAAA ,titre 2 : BBBB...)

ComboBox "titre tab 2" --> qui récupère les titres de la table 2 ( titre : CCCC , titre 2 : DDDD...)

quand je clique sur "titre tabe 1" et je choisi par exemple : AAAA dans le comboBox "ID" --> je récupére les ID ( 111 : P.interne , 120 : P.externe , etc..)

si je clique sur "titre tabe 2" et je choisi : DDDD dans le comboBox "ID" --> je récupére les "ID" ( 220 : ZZZZZ, etc ...)

puis dans le comboBox" entêtes " : (Nombre de personnel , nombre de missions , nombre de réunions , totales semaines )

si je choisi : AAAA ---> 111---> Nombre personnel dans résultat je dois obtenir comme ligne:

Numéro unique | Nom établissement | ID : libelle | Nombre de personnel |

1234567 | XXXXX | 111 : P.interne | 50

puis si : AAAA ---> 111---> Nombre missions

Numéro unique | Nom établissement | ID : libelle | Nombre missions |

1234567 | XXXXX | 111 : P.interne | 0

le but du formulaire et de pouvoir récupérer à la fin une liste des établissements :

Numéro unique |Nom de l'établissement |ID | Nombre de personnel | Nombre de missions | nombre de réunions |Total

semaines | ID | Nombre de personnel | Nombre de missions |nombre de réunions |Total semaines| ID etc ... = une ligne pour chaque établissements .

J'espère que j'ai pu vous expliqué à peut prêt même si ça me parait compliqué ça fait des jours que je cherche des solutions mais je me dis que c'est possible de réaliser ce genre de formulaire . Je compte sur vos idées .

Je vous souhaite une agréable soirée

Bonjour,

Comme tu dis que tous tes classeurs on la même structure (tableaux identiques) je suis parti non pas avec un système de recherche maius un affichage des va$leurs dans une ListView donc, je te laisse tester et revenir me dire !

Voici ton classeur :

Bonjour Theze , Bonjour tout le monde ,

Merci beaucoup l'idée est génial c'est très utile ça permet de visualiser sans perdre du temps . J'ai testé sur l'exemple et ça fonctionne très bien maintenant je vais essayer de l'adapter sur les fichiers de base .

Je reste quand meme sur l'idée "formulaire de recherche " car quand on a des fichiers > 100 et qu'on doit récupérer que certaine donné dans une liste pour des calculs ou autre ou je pense juste avec ce formulaire je puisse récupérer les données au cas ou je trouve quelque chose je posterai ici . Mais sinon , rien qu'avec celui là on gagne beaucoup de temps merci infiniment .

Passez une agréable journée

Bonjour Peerl le forum

bah moi je t'avais demandé un dossier avec plusieurs fichiers dans le même dossier, et zippé !!!

moi je t'aurais fait une une recherche automatique, aucune feuille à sélectionner ni même de dossier à choisir

et surtout dans le fichier de récupération les explications claires et précises des données à récupérer dans les divers fichiers

de toutes les feuilles (d'après ce que j'ai compris)

voilà à plus

Papou

Salut Papou

Je vais rajouter d'autres fichiers avec plus de précisions et zippé ( excuse moi je pensais que c'était suffisant ) .

à tout de suite

Re Peerl le forum

Non moi j'ai vu que theze te répondait, sachant que tu finirais par demander de l'automatisme, j'attendais simplement.

Car Theze lui, n'a fait que répondre à ta demande initiale, qui était de remplir une listbox et une autre listbox avec les feuilles !!!!

Donc sa réponse est parfaitement adaptée à ta demande initiale, mais cela sentait la demande d'automatisation ultérieure!!!!.

Pas trop quand même 5 ou 6 fichiers avec toutes les configurations possible de récupération et un exemple de ce que tu souhaites dans le classeur récupération( ou autre nom bien sur) en fait le classeur qui possèdera la macro de traitement

a+

Papou

Re Papou ,

je t'ai préparé les fichiers ( juste deux fichiers : j'ai essayé de varier les données sans changer le format des fichiers et des tableaux juste les données ) et j'ai essayé de données un résultat et d'expliquer brièvement le but de ce formulaire .

J'espère que cette fois ci j'ai pu mieux expliquer le besoin .

je suis là pour plus d'informations .

Merci bonne appétit

Re Theze

J'ai modifié le code dans listbox2_click et quand je lance le userform , j'ai un bug : " procédure trop grande "

Bonjour Peerl le forum

bon alors tu n'as pas l'air de vouloir trop t"impliquer dans ton sujet!!!

les fichiers sont tous formatés de la même manière ??? si oui pourquoi une fois un onglet nommé "onglet de Rercherche" et l'autre fichier "onglet recherché" ???

de plus pour les 5 premières lignes OK, mais ensuite tu as des titres aaaaaa et des ID 2 3 4 etc etc

je t'avais demandé de me faire un exemple, et bien sur, j'ai un tableau avec deux fois le jeu de colonnes!!!

il y a de ID à autre 3 soit 10 colonnes + les colonnes du début école etc

Mais avec le fichier classeur_7890.xlsx merci de me faire PROPREMENT et COMPLÉTEMENT en feuil3 de userform.xlsm manuellement exactement le tableau désiré .

a+

papou

re Papour

je suis navré que mes fichiers t'ont embêté , mais j'ai re'regardé l'onglet s'appel bien " onglet recherché " dans les deux fichiers je pense que ça doit être un bug ou autre de ma part surement .

Le jeu de colonnes c'est mon idée ( je suis novice en VBA ça se trouve ce n'est pas faisable ) et je l'ai écris manuellement ...enfaîte , j'ai besoin d'une liste qui permet de visualiser des données de chaque établissements . en gros , si je choisi L'id 1 par exemple et nombre personnel je dois avoir le numéro et le nom de l'établissement , l'id et son libellé et la colonne sélectionné ...or que si une deuxième fois je choisi un autre ID dans la même feuille du même établissement .. je vais avoir une deuxième ligne avec le même numéro de l'établissement etc .. d'ou l'idée de rester sur la même ligne ( jeu de colonnes ) .

Bon , je pensais que j'ai pu expliquer un peu ce que j'avais imaginé comme résultat je vais essayer de réécrire les tableaux...

Je m'excuse encore une fois

Re Peerl le forum

dans le fichier 7890 si tu fais comme tu le souhaites moi je veux bien mais il y a entre les titres et les ID au minimum 50 lignes multipliés par 10 colonnes +minimum 2 pour école et numéro, cela va faire la bagatelle de 600 colonnes !!!! (si tu as 50 lignes à récupérer)

Tu penses que cela va être lisible???

Le but final c'est quoi de chercher un élément particulier d'une feuille???

si oui il ne faut pas pratiquer comme cela

a+

Papou

Re Papou ,

Je me disais bien que c’était trop ... mais c'était juste une idée pour avoir une liste au lieu d'avoir 15 fichiers devant moi .

Le but est de pouvoir chercher un truc de particulier effectivement .

si je veux le chiffre du nombre de personnel par exemple par ID = 2 dans la feuille " onglet recherché" :

---> j'obtient une ligne : numéro unique | nom établissement | ID | libellé | Nombre personnel |

si je choisi que l'id 2 :

---> j'obtient une ligne : numéro unique | nom établissement | ID | libellé | Nombre personnel | et les autres colonnes aussi .

si j'arrive à faire c'est déjà mieux et rapide aussi .

je renvois les fichiers dans le prochain poste .

Merci Papou d’être patient

Ré perrl le forum

Ton ID c’est quoi ce qui est noté titre ?? Où les ID sous forme de chiffres?

C’est pour cela que je te demande de faire le tableau manuellement que je sache ce que tu veux dans le fichier’

J’attends ton fichier

A plus

Papou

Re,

Oui et bien là, ça devient n'importe quoi, tu n'as pas de rigueur dans ce que tu nous postes !

Les nouveaux classeurs sont bien différents du classeur "exemple" du premier envoi. Quand tu postes un fichier, il doit être le plus ressemblant possible à l'original c'est à dire qu'il faut faire une copie de l'original et anonymiser les valeurs qui sont soit confidentielles soit nominatives avec leur coordonnées (adresses, num téléphone, etc...) mais il ne faut pas envoyer n'importe quoi car sinon, nous perdons notre temps, toi comme nous, à faire ping-pong avec les questions réponses ! Forcément, mon précédent envoi ne représente pas ce que tu demandes à la vue des nouveaux fichiers postés !

Tu parles d'identifiant (ID) mais c'est quoi pour toi l'identifiant, le numéro unique ou autre chose ?

et comme le dit papou, construis un classeur ayant les résultats comme tu souhaites afin qu'on sache comment répondre.

Voici les fichiers zippé :

je pense bien vérifier cette fois ( je croise les doigts ) , l'onglet nommé : "onglet recherché " si ce n'est pas le cas ça sera juste une faute de frappe j'ai fais exprès de garder juste les feuilles que je dois utiliser qui ont la même structure .

Je n'ai pas effectué de changement de données . ( c'était nécessaire de bien remplir les tableaux ? )

Les ID c'est les chiffres en bleu .

puis j'ai mis deux feuilles dans USERFORM :

• résultat ID : si on selectionne que l'ID
  résultat ID_col : si on selectionne ID avec choix colonne .

C'est deux tableaux vous pensez que c'est mieux de les garder comme ça ou vaut mieux récupérer à chaque fois et sauvegarder sous et réutiliser la feuille résultat ? ( car chaque recherche à un objectif précis )

si vous avez de meilleurs idées je veux bien sachez que c'est juste pour éviter d'aller dans chaque fichier qui a onglets =>100 , chercher l'onglet puis des chiffres particuliers , les notés un par un pour des calculs ou autres..

Juste une question supplémentaire : si un jour je veux récupérer juste les "totale Titre" ou total par titre ? je ne sais pas comment l’intégrer dans mon tableau résultat . (actuellement je ne pense pas que j'en aurait besoin ) .

Je suis impliqué mais vraiment perdu . J'avoue que mon ignorance m'a poussé de faire n'importe quoi en pensant qu'avec un bout de tableau j'aurais une aide et pouvoir l'adapter par la suite ..excusez mon ignorance

Bon fin d'après midi