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,
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
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
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
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