VBA liste fichiers avec informations

Michel -  
michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   -
Bonjour tous le monde!

Je suis bloqué dans ma macro et ça a l'air assez balèze ...

Alors voilà, j'ai un dossier, avec 500 fichiers excel. Dans chaque fichier, il y a une seule feuille. Sur chaque feuille, il y a 2 case d'option (formulaire) qui permettent de savoir la fiche est complète ou non (c'est l'utilisateur qui coche lui-même).

J'ai créer un fichier excel qui permet de lister tous les fichier excel qui se trouvent dans le dossier (une colonne avec en A1 "Fichier" et le nom des fichiers à la suite en dessous.

J'aimerais maintenant que dans la colonne B, apparaissent à coter de chaque nom de fichier la mention "complet" ou "pas complet". 

Sub MAJ()

Dim Rep As String, Fichier As String
Dim i As Integer
Dim NomOnglet As String

NomOnglet = ActiveWorkbook.ActiveSheet.Name

i = 1
  
Rep = "C:\Users\...\...\TEST\"
Fichier = Dir(Rep & "*.xls")
Do While Fichier <> ""
    i = i + 1
    Sheets(NomOnglet).Range("A" & i) = Fichier
    Fichier = Dir
Loop

Columns("A:A").EntireColumn.AutoFit

Range("A2").Select

End Sub


Alors j'avais penser à ouvrir chaque fichier que je viens de lister, vérifier l'état des OptionButtons et en fonction de cet état, mettre la mention "complet" ou non.
... Seulement je ne sais pas du tout comment faire et ça prendrait la journée pour ouvrir 500 fichiers. Y a-t-il un moyen de faire cela sans ouvrir les fichiers?

Je ne sais pas si j'ai été assez clair mais d'avance merci du temps que vous passerez ... ça à l'air d'être un sacré défi!

Cordialement

10 réponses

  1.
    michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
     
    Excusez moi d'avoir dérangé en essayant d'aider
    1
    1. Michel
       
      Bonjour michel_m,

      Désolé de ne pas avoir répondu, ce n'est pas correct... mais je continuais d'essayer d'utiliser le morceau de code que tu m'as donné. Et surtout de comprendre comment il fonctionne..
      0
    2. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
       
      si tu prends la solution d'ouvrir chaque fichier

      tu peux bosser sur une seule case d'option; par ex case d'option 1 qui revoie "fait" si le bouton radio est activé 

      Sub donner_etat() 'A ADAPTER
      Dim Etat As String
              Workbooks("Classeur1.xlsx").Activate
              ActiveSheet.Shapes("Option Button 1").Select
             Etat = IIf(Selection.Value = xlOn, "fait", "pas fait")
      End Sub

      mais ca va ^tre très long

      en début de macro n'oublie pas de mettre quand m^me cette ligne 

      application.screenupdating=false


      maintenant, si toi ou tes collègues mettent à jour les "fait-pas fait" assez souvent, tu aurais peut-^tre intérêt à faire une macro, qui une bonne fois pour toutes sur les 500 classeurs, créerait une cellule liée commune aux 2 options. l'investissement en temps serait égal à a peu près la m^me chose que chaque mise à jour

      un exemple de recherche rapide et simple malgré les dires de tyrannausor avec une macroXL4 sans ouvrir les fichiers.
      http://www.cjoint.com/c/FGenKG2LXst

      nota: extraire le zip et non l'ouvrir
      0
    3. Michel
       
      Merci beaucoup michel_m!

      au final maintenant, j'ai près de 2000 fichier excel à classer ... et je viens de faire le test de ma macro, elle liste 150 fichier en environ 1 min. Comme j'ai repris des morceaux de codes un peu partout sur les forums, je pense que j'ai beaucoup de lignes inutiles et qu'il y a beaucoup plus simple... mais bon on met le fichier à jour seulement 1 fois par semaine donc ça devrait aller...

      peux-tu m'expliquer ce que fait : 

      application.screenupdating=false
      ?

      Sinon, je pense que je vais encore chercher un petit peu à alléger la macro...

      Merci infiniment de ton temps!
      0
  2. tyranausor Messages postés 3501 Date d'inscription   Statut Membre Dernière intervention   2 060
     
    Bonjour, avec ton fichier contenant la macro et un des fichiers à vérifier (sans données perso) on pourrait t'aider. Tu compresse le tout et utilise cjoint.com pour le transmettre
    0
    1. Michel
       
      Bonjour tyranausor et merci pour ta réponse,

      Voici le lien : http://www.cjoint.com/c/FGbibBJKZv5

      J'espère que tu pourras m'aider et que je n'abuse pas trop de ton temps...
      0
  3. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
     
    Bonjour,

    EDIT: 12,30H

    Pour travailler sans ouvrir les classeurs, il faut qu'il y ait une cellule commune qui dise 1 ou 2 suivant l'option bouton activée.(dans "propriétés")
    mais j'en ai pas vu dans tes classeurs
    Donc, tu vois le boulot...
    mais si on a cette cellule "1-2" toujours à la m^me adresse on n'a pas besoin d'ouvrir les fichiers en utilisant une macro XL4

    un exemple de cette macro XL4 pour le principe (syntaxe à aménager au cas par cas) 
    Cells(2, 2) = ExecuteExcel4Macro("'" & chemin & "\[source.xls]Feuil1'!R2C1") 'R2C1=A2


    de +,

    si tu boucles sur tous les classeurs sources dans le dossier, pourquoi les énumérer dans ton recap ?
     Michel
    0
  4. tyranausor Messages postés 3501 Date d'inscription   Statut Membre Dernière intervention   2 060
     
    Bonjour, je pense plutôt à avoir dans un cellule du fichier à vérifier un booléen (vrai/faux ou complet/incomplet) car il n'est pas possible de savoir, sans ouvrir le fichier, si un checkbox est cochée
    0
    1. Michel
       
      Bonjour tyranausor,

      Désolé pour le retard de ma réponse...

      Je pense qu'au final, ce n'est pas dérangeant de devoir ouvrir chaque fichier excel... Penses-tu que c'est possible en ouvrant chaque fichier?

      Merci beaucoup de ta réponse
      0

  5. Vous n’avez pas trouvé la réponse que vous recherchez ?

    Posez votre question
  6. tyranausor Messages postés 3501 Date d'inscription   Statut Membre Dernière intervention   2 060
     
    Bonjour, si tu préfère, la macro peut ouvrir chaque classeur pour rechercher l'information ce qui évite par ailleurs e travailler avec des fichiers fermé ce qui complique moins les choses! Après, il faudra que tu dise si tu veux voir ou non les opérations à l’écran c'est-à-dire avoir un aperçu en direct
    0
  7. Michel
     
    Et bien, si c'est possible en ouvrant chaque fichier d'aller rechercher l'info, alors ok :)

    Et pour voir ou non les opérations à l'écran, je ne sais pas ce qui est le plus compliqué à coder ...

    Et j'ai remarqué que ce code fonctionne : 

    Sub Test_case_option()

    Dim NomOnglet As String
    NomOnglet = ActiveWorkbook.ActiveSheet.Name
     
    With Sheets(NomOnglet)
     
    Select Case xlOn
    Case .OptionButtons(1).Value
    MsgBox "case Fait sélectionnée"
     
    Case .OptionButtons(2).Value
    MsgBox "case Pas Fait sélectionnée"
     
    End Select
    End With
    End Sub


    Est-ce que ça veut dire que les cases option renvoient une information? Dans ce cas là, je pourrais l'adapter à mon problème :

    Pour chaque fichier excel dans le dossier "C:\Users\...\...\TEST\"
    Ecrire le nom du fichier dans la colonne A
    Ouvrir le fichier
    Si case.OptionButtons(1) cochée alors
    Activer "Fichier liste"
    Ecrire dans colonne B "Fait"
    Sinon si case.OptionButtons(2) cochée alors
    Activer "Fichier liste"
    Ecrire dans colonne B "Pas Fait"
    Fermer le fichier

    ... Vous pensez cela réalisable?

    Merci beaucoup pour votre temps!
    0
    1. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
       
      re,
      dans tes classeurs source tu n'as pas besoin de tester les 2 boutons
      quand tu en actives 1, automatiquement, l'autre se désactive...

      Si case.OptionButtons(1) cochée alors
      Activer "Fichier liste"
      Ecrire dans colonne B "Fait"
      Sinon
      Activer "Fichier liste"
      Ecrire dans colonne B "Pas Fait"

      150 fichiers: 1 minute ----> 2000 fichiers >=13 mn....alors qu'on devrait parler en secondes

      cependant:on pourrait quand m^me gagner pas mal de temps avec des variables- tableaux: j'va regarder çà

      ce qui est très lent avec VBA-excel c'est les allers et retours entre le processeur, la ram, la carte graphique et l'écran (pour l'anecdocte, taper la lettre A à l'écran demande > 100 instructions binaires) et ouvrir un classeur est toujours très lent car c'est un objet complexe. c'est pour cela que pour un grand nombre de classeurs, on travaille en fichiers fermés.
      On paye là l'erreur grave de conception du classeur ( oubli de la cellule liée)
      0
  8. tyranausor Messages postés 3501 Date d'inscription   Statut Membre Dernière intervention   2 060
     
    application.screenupdating=false signifie que l'écran ne se rafraichit pas, pas de mise à jour. Autrement dit, tu ne verras pas la macro travailler
    0
  9. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
     
    Bonjour

    proposition: 0,4 secondes pour 3 classeurs :-/
    (extrapolation: pour 150 classeurs: 20 secondes au lieu de 60....bof!!!)

    http://www.cjoint.com/c/FGffkEa5gst 

    Option Explicit
    '----------------------------------------------------------
    Sub recapituler_etats()
    Dim T_etats, Chemin As String
    Dim Fichier As String, Cptr As Integer
    Dim Start As Single

        Start = Timer 'mesure durée exécution _
        à supprimer après essais
    '-----------------------------------initialisations
        'fige défilement écran: confort, rapidité
        Application.ScreenUpdating = False
        With Sheets("feuil1")
            .Range("A2:B5000").Clear  'nettoyage
            'préparation tablo fichier -état
            ReDim T_etats(1 To 5000, 1 To 2)
            'emplacement des fichiers d'états A ADAPTER
            Chemin = ThisWorkbook.Path & "\" & "fichiers" & "\"
            
    '------------------------------recherche etat dans les fichiers
            'déplacement dans le dossier des classeurs-état
            ChDir Chemin
            Fichier = Dir(Chemin & "*.xls")
            While Fichier <> ""
                    Cptr = Cptr + 1
                    Workbooks.Open (Fichier)
                        'mémorisation du nom de classeur
                        T_etats(Cptr, 1) = Fichier
                        'mémorisation de l'état du classeur
                        With ActiveSheet
                            Select Case xlOn
                                Case .OptionButtons(1).Value
                                    T_etats(Cptr, 2) = "fait"
                                Case .OptionButtons(2).Value
                                    T_etats(Cptr, 2) = " pas fait"
                            End Select
                        End With
                    ActiveWorkbook.Close
                Fichier = Dir
            Wend

    '----------------------Récapitulatif de l'état par classeur
           With .Range("A2").Resize(Cptr, 2)
                .Value = T_etats
                .Borders.Weight = xlThin
            End With
        End With
        'a supprimer après essais
        Application.ScreenUpdating = False
        MsgBox "durée: " & Timer - Start & " secondes."
        
    End Sub


    0
    1. Michel
       
      Merci infiniment michel_m pour tes explications! Je vais essayer cette macro avec 2000 fichier, et vais voir si ça fonctionne toujours! Chez moi, environ 18 secondes pour 30 fichiers

      Merci encore!
      0
  10. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
     
    re moi,

    pour le fun, je suis tombé à 3/100 de seconde pour les 3 fichiers...

    si ca t'intéresse tu fais signe
    attention dans ton classeur "135" il ya une discordance ente le nom du fichier er le nom de l'onglet : manque un espace dans la parenthèse du nom de fichier :-(

    présentation de la bidouille:
    2 classeurs: 1 qui construit la cellule liée (c'est long mais on le fait qu'une fois) et à prévoir pour de nouveaux classeurs (peut-^tre créer un modèle).
    une fois créée la cellule liée,L'autre exécute la macro xl4 sans ouvrir les fichiers. de plus le code est + simple (au passage: toujours tourner 7 fois la souris sur le tapis avant de .... etc)

    calcul théorique pour 2000 lignes= 20 secondes au lieu de 13 minutes prévues au départ
     Michel
    0
    1. Michel
       
      ah oui effectivement ce serait vraiment plus pratique... Je vais réfléchir à une macro qui construirait des cellules liées aux OptionsButton!

      Sinon j'ai essayé avec les 2000 fichiers et j'obtient le message d'erreur suivant : 

      Erreur d’éxécution’1004’ :
      ’nom_d'un_des_fichiers.xls’ introuvable. Vérifiez l’orthographe du nom du classeur et la validité de l’emplacement.
      Si vous essayez d’ouvrir le fichier à partir de la liste des fichiers les plus récents, assurez-vous que le fichier n’a pas été renommé, déplacé ou supprimé.


      je ne comprends pas car le chemin est bon et le fichier s'y trouve...

      Je vais chercher d'où peux venir le problème!

      Merci beaucoup pour le temps que tu m'as accordé :)
      0
    2. Michel
       
      Pour info, le bug intervient à la ligne : 

      Workbooks.Open (Fichier)
      0
    3. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320 > Michel
       
      ci joint les 2 macros en question: "créer_cellule" et "recap_vite"

      http://www.cjoint.com/c/FGfiOSYa1it

      tu dis le temps por récapituler tes 2000 classeurs

      pour le bug, je ne sais pas...

      mais chez les 3 classeurs sont en xlsx et dans les macros en .xls: il faudrait que ca soit la m^me chose et éventuellement que tu mettes les classeurs avec le m^me suffixe et modifier les macros
      0
    4. Michel
       
      On est obliger d'aller chercher le nom de l'onglet? 

      Etat = ExecuteExcel4Macro("'" & Chemin & "\[" & Fich & "]" & Onglet & "'!R2C52")


      Car j'ai des fichier où le nom de l'onglet ne correspond pas au nom du fichier...
      0
    5. Michel
       
      Crois-tu que c'est possible de récupérer le nom du premier onglet d'un fichier excel fermé?

      J'ai bien essayé ça : 

      Onglet = Sheets(1).Name
      Etat = ExecuteExcel4Macro("'" & Chemin & "\[" & Fich & "]" & Onglet & "'!R2C52")


      mais ça me dit que Onglet est égal à "Feuil1" ... il faudrait préciser que 

      Onglet = Sheets(1).Name 'de chaque Fich


      je cherche encore, je cherche...
      0
  11. tyranausor Messages postés 3501 Date d'inscription   Statut Membre Dernière intervention   2 060
     
    Bonjour, je viens avec une autre propositions avec des boutons radio en ActiveX avec une cellule liée indiquant l'état.

    http://www.cjoint.com/c/FGfkEftcigB

    Tu me diras ce que tu en pense
    0
    1. Michel
       
      Merci, je regarde ça :)
      0