Recherche données dans plusieurs classeurs et onglets

Bonjour,

Je travaille sur 10 classeurs de 10 onglets chacun. Chaque onglet liste des commandes. Je souhaiterais créer une requête dans un 11ème classeur, qui listerait parmi tous les onglets de tous les classeurs, les commandes passées depuis plus de 2 mois et non payées.

En PJ un tableau avec 2 onglets de commandes où j'ai surligné les commandes répondant aux critères de recherche (mise en forme conditionnelle) et un 3ème "Relances" qui ressemble à ce que je voudrais.

Quelqu'un peut m'aider ?

Aurélien.

Salut Aurélien,

Je vois que tu n 'as pas trop de succès avec ta demande Malgré mes maigres connaissances, je veux bien essayer de t'aider.

Il serait peut-être intéressant que tu ailles voir le fil https://forum.excel-pratique.com/excel/regrouper-les-donnees-de-differents-fichiers-t19600.html qui correspond selon moi assez bien à ton problème, ça te donnera peut-être des idées.

Autrement, peux-tu fournir plus de renseignements :

1) Est-ce que tous tes fichiers existants sont placés exactement au même endroit dans ton Explorer, donc ont-ils tous le même chemin d'accès ?

2) Ont-ils des noms à répétition du genre "Résultats de Jean", "Résultats de Jacques" ou ont-ils chacun un nom particulier ?

3) Sont-ils protégés par des mots-de-passe ?

4) Leur nombre de 10 peut-il varier à l'avenir, un fichier peut-il être supprimer, un autre nouvellement créé ?

A te relire.

Bonjour Yvouille,

Désolé de te répondre tardivement, je n'avais pas vu ton message.

En tout cas merci pour le lien, je vais aller regarder, ça a l'air intéressant effectivement.

Pour ce que tu demandes, 12 classeurs sont dans un dossier appelé "2011". Dans ce dossier il y a également un sous-dossier "Suivi Crèches 2011" avec 11 classeurs (j'avais donné un nombre à la louche, j'étais très en dessous).

Tous les classeurs sont nommés "2011_xxx", les xxx varient (ex: 2011_020 ADMINISTRATION ou 2011_0209 ANIMATION). Les onglets ont également des noms qui changent du type 60611, 60612, 60632 (ce sont des codes d'imputation budgétaire).

Aucun classeur n'est protégé par mot de passe.

Enfin, dans l'absolu un classeur peut être créé ou supprimé, mais ça n'arrive pour ainsi dire jamais (pas depuis 3 ans en tout cas).

Cordialement.

Salut Aurélien,

J'ai préparé un début de solution. C'est passablement bricolé, mais ça fonctionne bien chez moi. Je ne peux placer normalement que deux fichiers sur ce fil, alors je joins le troisième fichier d'une autre manière.

J'ai mes deux fichiers 2011_020 ADMINISTRATION et _0209 ANIMATION placés à un endroit précis de mon arborescence, de manière à pouvoir utiliser leur chemin d'accès (voir dans le code, "Chemin Yvouille"). Si tu pouvais déplacer tous tes fichiers au même endroit, on pourrait n'utiliser qu'un seul chemin. Sinon il faudra voir comment on pourra en utiliser deux. Ces deux fichiers restent fermés lorsqu'est lancée la macro inscrite dans le troisième fichier.

Tu ouvres donc ce troisième fichier "Relance" et tu lances la macro par le bouton mis en place. Ca ne fonctionnera bien entendu pas si tu ne corriges pas le chemin d'accès (j'ai préparé un départ de code "Chemin Aurélien") en fonction de l'emplacement de ces fichiers chez toi.

J'ai dû modifier un peu mes fichiers joints de manière à ce qu'ils aient tous la même structure. Si jamais, il faudra soit corriger la structure de tes fichiers réels, soit qu'on modifie le code en fonction de la structure que tu voudrais garder (mais alors il faudrait que tu te décides pour une seule structure pour tous les fichiers).

Tu avais des feuilles masquées nommées "Données" ; je les ai supprimées, car elles ne me paraissaient pas utiles. Si jamais elles le sont, il faudra aussi voir comment faire à ce propos.

1) J'ai inscrit la date des fichiers (2011) en N2 et utilise cette référence dans le code, de manière à ce qu'il soit à nouveau réutilisable en 2012, 2013, etc., simplement en changeant la valeur de cette cellule (car j'imagine que tu vas modifier le nom de tes fichiers par 2012_020 ADMINISTRATION, etc.) : 2) Il y a une date référence en Z1 (deux mois avant la date du jour) et 3) tu devrais inscrire une fois pour toute le nom de tous les fichiers que tu veux traiter dans la colonne N (je n'ai inscrit moi-même que les deux fichiers test). Fais bien attention de ne pas effacer par erreur les informations indiquées aux points 1), 2) et 3) ci-dessus !!!!!!!!

Je crois que je vais m'arrêter là pour cette fois.

A te relire.

Le troisième fichier :

Bonjour Yvouille,

Encore désolé pour cette nouvelle réponse tardive, j'étais en vacances. JE te remercie vivement pour ce travail. Je vais étudier ça de près mais de prime abord ça a l'air parfait !

A bientôt.

Aurélien.

-- 23 Fév 2011, 14:37 --

Bonjour Yvouille, bonjour à tous,

J'ai donc zyeuté avec attention et ça fonctionne parfaitement sur les fichiers test. Merci ! Mais comme tu le faisais remarquer, un problème de structure des classeurs va se poser.

Je m'explique, mes vrais classeurs ont des structures différentes (et il est délicat de modifier cette structure) : au minimum 5 onglets et au maximum 31 onglets. Chaque classeur possède un onglet "RECAP" et un onglet "Aide saisie" (qui ne doivent pas être scannés) et donc un nombre variable (et qui peut évoluer) d'onglets d'imputation budgétaire. J'ai fait un test en ajoutant un onglet d'imputation sur un des 2 classeurs "Test" et la macro fonctionne à merveille, elle va bien chercher les données sur le nouvel onglet aussi. La difficulté consiste donc à faire en sorte de ne pas scanner l'onglet RECAP et l'onglet Aide saisie. Peut-on dans ce cas rajouter une ligne de code spécifiant cette exception ?

Là ou ça se complique, c'est qu'en plus sur 2 classeurs, il y a des onglets "Suivi" qui ne doivent pas être scannés non plus. Et là où ça se complique définitivement, c'est je ne suis pas certain d'avoir l'autorisation de remonter dans l'arborescence les classeurs qui sont dans le sous-dossier "Suivi crèches".

Pour que tout ça soit plus clair, je joins un fichier de la structure de différents fichiers (en rouge les éléments qui ne doivent pas être scannés).

Je vais me pencher sur ta macro pour déjà essayer de comprendre son fonctionnement (même si je perçois bien l'idée, il faut que ej déchiffre les codes) et réfléchir à comment la modifier pour prendre en compte tous les paramètres.

Cordialement,

Aurélien.

Salut Aurélien,

Comme tu as édité ton message au lieu d'y répondre (mais as-tu seulement choisi cette option ou t'a-t-elle été imposée sans que tu ne t'en rendes compte ?) je n'ai pas reçu de courriel m'en informant immédiatement. C'est donc par pur hasard que j'ai découvert il y a quelques minutes ton complément de message du 23.02 à 14:37. Désolé.

Que tes classeurs aient une ou 36 feuilles, ça n'a pas trop d'importance, puisque le code proposé fait trois boucles différentes, dont la deuxième passe dans chacune des feuilles de chacun des fichiers listés (la première boucle passe en revue chacun des fichiers à traiter selon la liste que je t'ai indiqué d'établir dans la colonne N et la troisième boucle passe dans chacune des lignes concernées). Ce qui sera plus compliqué, ce sera d'éviter les feuilles que tu as indiquées en rouge. J'ai peur que toutes tes feuilles dont il faut extraire des données n'aient pas toutes la même structure (mon code recherche pour l'instant les informations de la dixième à la dernière ligne et selon des titres bien précis). Si nécessaire, peux-tu m'envoyer un exemple de chacune des "sortes de feuilles" à traiter ? Apparemment, on ne pourra pas travailler sur le nom des onglets puisque certains onglets à éviter portent également des numéros. Par contre je pense qu'on pourra travailler en plaçant un signe distinctif dans chacune des feuilles à éviter, par exemple un 1 dans la cellule AI65000. Donc, si tu as de nouvelles feuilles qui seront rajoutées, ça ne devrait - de prime abord - pas causer de problème, à condition qu'elles aient ou non ce signe distinctif.

Si tu n'as pas la possibilité de déplacer les fichiers dans l'arborescence, peux-tu m'indiquer combien il y a de chemins différents (à combien d'endroits sont placés tes fichiers) ? Dans l'uns de tes messages précédents tu parlais d'un dossier et d'un sous-dossier. J'espère que ça s'arrête là, à ce total de deux chemins ! Apparemment oui, selon les détails fournis dans ton dernier fichier Excel. Il faudra alors probablement faire une quatrième boucle, passant en revue ces deux chemins.

A propos du passage ci-dessous, on pourrait éviter des problèmes d'effacements non désirés en plaçant ces informations sur une feuille séparée. Je n'avais pas voulu le faire la première fois, ne sachant pas dans quelle mesure tu voulais aller plus loin dans ce projet :

Yvouille a écrit :

1) J'ai inscrit .................2) Il y a une date référence en Z1 ................ et 3) tu devrais ............... Fais bien attention de ne pas effacer par erreur les informations indiquées aux points 1), 2) et 3) ci-dessus !!!!!!!!

Qu'en est-il des feuilles indiquées ci-dessous ?

Yvouille a écrit :

Tu avais des feuilles masquées nommées "Données" ; je les ai supprimées, car elles ne me paraissaient pas utiles. Si jamais elles le sont, il faudra aussi voir comment faire à ce propos.

Bonjour Yvouille,

Beaucoup de travail dernièrement, donc je n'ai pas pu te répondre plus tôt.

C'est effectivement bizarre que mon message soit apparu comme "édité" car j'avais bien cliqué sur "Répondre". M'enfin, revenons à nos moutons.

Les feuilles à scanner ont bien toutes la même structure :

Colonne A : n° de commande

Colonne B : date

Colonne C : fournisseur

Colonnes D à H : désignation (j'ai ajouté 2 colonnes justement pour homogénéiser les tableaux, car selon les tableaux le nbre d'infos à indiquer change)

Colonne I : engagé

Les titres sont en ligne 13, les infos, donc, à partir de la ligne 14. Le seul écart de structure peut se retrouver sur le titre "désignation". Les cellules D13 à H13 sont fusionnées mais j'aurai a priori besoin sur certains onglets d'ajouter un autre titre, ce qui donnerait quelque chose comme : cellules D13 à F13 = "désignation" et G13 à H13 = "autre titre". Est-ce problématique à ton avis ? (Je joins un le fichier modifié avec les 2 nouvelles colonnes sur chaque onglet)

L'idée du signe distinctif dans une cellule choisie me semble effectivement une excellente solution de contournement du problème.

Je te confirme qu'il n'y a bien que 2 chemins (même si je garde espoir de pouvoir supprimer ce sous-dossier qui est plus embêtant qu'autre chose).

Dans le fichier "Relance", on peut effectivement mettre les infos à ne pas effacer sur un autre onglet ou bien simplement protéger la feuille, mais peut-être que la protection parasiterait la macro ?

Enfin, les onglets "Données" sont effectivement parfaitement inutiles.

En tout cas, merci vivement du temps que tu passes sur ce projet, Yvouille, ton aide m'est précieuse !

Aurélien.

Salut Aurélien,

Aurélien L a écrit :

Les titres sont en ligne 13, les infos, donc, à partir de la ligne 14.

Aurélien L a écrit :

Le seul écart de structure peut se retrouver sur le titre "désignation".

Les données correspondant à "Engagé" étaient tout d'abord dans tes colonnes G, maintenant dans les colonnes I. J'espère que ça ne va pas changer encore plusieurs fois

Aurélien L a écrit :

Dans le fichier "Relance", on peut effectivement mettre les infos à ne pas effacer sur un autre onglet ou bien simplement protéger la feuille, mais peut-être que la protection parasiterait la macro ?

J'ai placé un signe distinctif dans les feuilles à ignorer dans les cellules L1. Finalement, si c'est possible, il faudrait peut-être également laisser ce signe visible, afin de ne pas l'effacer par erreur et afin que tu saches clairement que ces feuilles ne sont pas prises en compte.

J'ai modifier le code afin que deux chemins soient utilisables (les fichiers sont maintenant listés dans les colonnes N et O selon le chemin auquel ils correspondent.

Je joins mes trois nouveaux fichiers-exemple - placés chez moi dans deux dossiers différents - ainsi que mon nouveau fichier de base.

Attention : j'ai modifié le code afin de simplifier la référence aux fichiers. DAns la liste des fichiers à traiter tu dois indiquer

2011_

020 ADMINISTRATION_V3

0209 ANIMATION_V3

au lieu de

2011

_020 ADMINISTRATION_V3

_0209 ANIMATION_V3

A te relire

Bonjour Yvouille,

Je viens de tester et ça fonctionne à merveille !

Je te confirme que la structure actuelle est définitive. J'avais du ajouter des lignes juste après mon 1er message, sans penser à te le dire, je m'en excuse. Les titres sont donc bien en ligne 13. Pour modifier cela dans la macro, il suffit que je note For k=14 au lieu de For k=10, non ?

For k = 10 To DerLigFeuille_traitée 'Nombre de lignes à traiter

Pour le reste, je serai, effectivement, probablement l'unique utilisateur de ce fichier, donc je pense le laisser tel quel pour le moment. En cas de nécessité, je le modifierai ultérieurement.

Encore un grand merci pour ce travail Yvouille qui correspond parfaitement à mes attentes.

Aurélien L a écrit :

Pour modifier cela dans la macro, il suffit que je note For k=14 au lieu de For k=10, non ?

Si tu considères que tu as tes réponses, indiques ton fil comme "Résolu".

Bonne journée.

Fait.

MERCI.

Aurélien.

-- 04 Mar 2011, 15:12 --

Re,

En fait j'ai repéré un problème que je n'arrive pas à solutionner. Cela concerne le "Ignorer" placé en L1 sur les feuilles qui ne doivent pas être scannées.

For j = 1 To Sheets.Count 'Nombre de feuilles dans le fichier
                    Sheets(j).Activate

                    If Range("L1") = "Ignorer" Then 'Pour signifier les feuilles à ne pas traiter
                    Exit For
                    End If

                    Feuille_traitée = Sheets(j).Name
                    DerLigFeuille_traitée = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Le problème : dès que la macro trouve une feuille avec "Ignorer", elle ne la scanne pas, ce qui est le but recherché sauf qu'au lieu de passer à la feuille suivante, elle passe au classeur suivant et ignore les autres feuilles même s'il n'y a pas de"Ignorer" en L1.

Que puis-je faire ?

Aurélien

-- 04 Mar 2011, 16:10 --

J'ai trouvé !

En fait j'ai supprimé le code suivant

If Range("L1") = "Ignorer" Then 'Pour signifier les feuilles à ne pas traiter
                   Exit For
                    End If

Et je l'ai ajouté à la condition qui existait déjà

If ActiveCell.Offset(0, 1) < Date_limite And ActiveCell.Offset(0, 9) >= 0 And ActiveCell.Offset(0, 9) <> ActiveCell.Offset(0, 8) And Range("L1") <> "Ignorer"

J'ai finalement choisi de déplacer les données-clé sur un autre onglet.

Je sais pas si ça peut intéresser quelqu'un mais je place le code en entier, à tout hasard. Et encore merci Yvouille pour le temps consacré à ma demande !!!

Sub a()

Dim Référence As String, Fichier_traité As String, Feuille_traitée As String, DerLigFeuille_traitée As Integer, DerLigA_Relance As Integer
Dim i As Integer, j As Integer, k As Integer, n As Integer, m As Integer, Date_limite As Date
Dim a As String, b As Date, c As String, d As String, e As String, f As String, g As Currency, h As Currency

Application.ScreenUpdating = False
Call Effacer

Date_limite = Sheets("Données").Range("C2")  'Date = aujourd'hui + 2 mois

For n = 1 To 2 ' car 2 chemins possibles
    If n = 1 Then
    m = Sheets("Données").Range("A" & Rows.Count).End(xlUp).Row
    Else
    m = Sheets("Données").Range("B" & Rows.Count).End(xlUp).Row
    End If

    For i = 3 To m 'Nombre de fichiers listés en A et B

        Référence = Sheets("Données").Range("A2")

        If n = 1 Then
        Fichier_traité = Sheets("Données").Range("A" & i).Value
        Workbooks.Open Filename:="C:\Documents and Settings\leblanca.POPULATION3\Bureau\Test relance\V2\" & Référence & Fichier_traité
        Else
        Fichier_traité = Sheets("Données").Range("B" & i).Value
        Workbooks.Open Filename:="C:\Documents and Settings\leblanca.POPULATION3\Bureau\Test relance\V2\Sous-dossier\" & Référence & Fichier_traité
        End If

                For j = 1 To Sheets.Count 'Nombre de feuilles dans le fichier
                    Sheets(j).Activate

                    Feuille_traitée = Sheets(j).Name
                    DerLigFeuille_traitée = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

                            For k = 10 To DerLigFeuille_traitée 'Nombre de lignes à traiter
                            Range("A" & k).Select

                                If ActiveCell.Offset(0, 1) < Date_limite And ActiveCell.Offset(0, 9) >= 0 And ActiveCell.Offset(0, 9) <> ActiveCell.Offset(0, 8) And Range("L1") <> "Ignorer" Then 'Indique les conditions de la recherche
                                        a = ActiveCell.Offset(0, 0) 'No de commande (colonne A)
                                        b = ActiveCell.Offset(0, 1) 'Date (colonne B)
                                        c = ActiveCell.Offset(0, 2) 'Fournisseur (colonne C)
                                        d = ActiveCell.Offset(0, 3) 'Désignation (colonne D)
                                        e = ActiveCell.Offset(0, 4) 'Désignation (colonne E)
                                        f = ActiveCell.Offset(0, 5) 'Désignation (colonne F)
                                        g = ActiveCell.Offset(0, 8) 'Engagé (colonne I)
                                        h = ActiveCell.Offset(0, 9) 'Liquidé (colonne J)

                                        Windows("Relance.xls").Activate
                                        DerLigA_Relance = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
                                        Range("A" & DerLigA_Relance + 1) = Fichier_traité
                                        Range("B" & DerLigA_Relance + 1) = Feuille_traitée
                                        Range("C" & DerLigA_Relance + 1) = a 'No de commande
                                        Range("D" & DerLigA_Relance + 1) = b 'Date
                                        Range("E" & DerLigA_Relance + 1) = c 'Fournisseur
                                        Range("F" & DerLigA_Relance + 1) = d 'Désignation
                                        Range("G" & DerLigA_Relance + 1) = e 'Désignation
                                        Range("H" & DerLigA_Relance + 1) = f 'Désignation
                                        Range("I" & DerLigA_Relance + 1) = g 'Engagé
                                        Range("J" & DerLigA_Relance + 1) = h 'Liquidé
                                        Windows(Référence & Fichier_traité & ".xls").Activate
                                End If
                        Next 'k
                Next 'j

            ActiveWorkbook.Close SaveChanges:=False
    Next 'i
Next 'n

    ActiveWindow.SmallScroll Down:=-10000
    Range("A1").Select
End Sub
Sub Effacer()
    Application.ScreenUpdating = False
    Range("A7:J65000").ClearContents
    Range("A1").Select
End Sub

Aurélien L a écrit :

J'ai trouvé !

Quelle honte que je n'ai pas mieux contrôlé les résultats obtenus. Lorsque j'ai vu que plusieurs données étaient reportées - avec ou sans la balise "Ignorer" - je n'ai pas pensé d'aller contrôler feuille après feuille.

Mais j'aurais dû le faire

Désolé !!!

Sans rancune, j'espère

Tu rigoles Yvouille ? C'est toi qui as fait tout le travail ! Sans toi, j'aurais jamais pu faire ça.

Et pour en finir avec ce post, j'ai bien fini par trouver la solution mais ce n'est pas celle que j'ai écrit vendredi soir. Ta ligne de code If Range ("L1") = "Ignorer" Then Exit For End If était la bonne mais placée au mauvais endroit. Au lieu de la mettre dans le For j, je l'ai placée dans le For k. Ca fonctionne bien, la macro passe bien chaque feuille en revue. Hourra!

A plus le forum.