VBA liste fichiers avec informations

Michel -
michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention - 5 juil. 2016 à 16:04

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

Afficher la suite

A voir également:

VBA liste fichiers avec informations
Liste déroulante excel - Guide
Entrer les informations d'identification reseau - Guide
Liste déroulante en cascade - Guide
Liste code ascii - Guide
Renommer des fichiers en masse - Guide

10 réponses

Réponse 1 / 10

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

Excusez moi d'avoir dérangé en essayant d'aider

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

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

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

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!

Réponse 2 / 10

tyranausor Messages postés 3545 Date d'inscription Statut Membre Dernière intervention 2 039

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

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

Réponse 3 / 10

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

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

Réponse 4 / 10

tyranausor Messages postés 3545 Date d'inscription Statut Membre Dernière intervention 2 039

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

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

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

Posez votre question

Réponse 5 / 10

tyranausor Messages postés 3545 Date d'inscription Statut Membre Dernière intervention 2 039

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

Réponse 6 / 10

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!

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

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)

Réponse 7 / 10

tyranausor Messages postés 3545 Date d'inscription Statut Membre Dernière intervention 2 039

application.screenupdating=false signifie que l'écran ne se rafraichit pas, pas de mise à jour. Autrement dit, tu ne verras pas la macro travailler

Réponse 8 / 10

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

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

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!

Réponse 9 / 10

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

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

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é :)

Michel

Pour info, le bug intervient à la ligne :

Workbooks.Open (Fichier)

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314 > 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

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

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

Réponse 10 / 10

tyranausor Messages postés 3545 Date d'inscription Statut Membre Dernière intervention 2 039

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

Michel

Merci, je regarde ça :)

Votre réponse