[VBA] Lister et répertorier le contenu d'un dossier Windows

[Ripley511]

Bonjour à tous,

Tout d'abord, permettez-moi de vous remercier pour ce superbe forum où je trouve nombre de solutions à mes problématiques Excel !

Mais après avoir épluché tous les sujets, essayé d'adapter toutes les solutions approchantes, je reste sur un échec pour mon challenge le plus récent !

Je mets actuellement en place un système de suivi budgétaire. Toute la partie "commande" est faite, mais il me reste la partie "Devis" (donc avant la commande) à traiter. Comme l'entreprise où je travaille est peu procédurière, j'ai opté pour une solution simple :
- Placer tous les devis reçus dans un dossier "Devis_reçus"
- Copier les devis retenus (validés) dans un dossier "Devis_validés"

Néanmoins, j'aimerais pouvoir tenir une liste de suivi des devis. J'exigerai des personnes enregistrant un devis un nom de fichier répondant à la charte suivante :

[Date au format AAAAMMJJ]_[Initiales du commanditaires]_[Intitulé du devis]_[Fournisseur].pdf

Donc, ma liste Excel devra :
- Lister automatiquement les fichiers contenus dans le dossier "Devis_reçus" (inutile de retenir les devis validés, puisque 1) ils seront déjà dans "Devis_reçus" et 2) ils feront l'objet d'un bon de commande que je suis ailleurs) ;
- Répertorier/répartir les informations dans plusieurs colonnes, par exemple :
Colonne A : Intitulé du devis
Colonne B : Fournisseur
Colonne C : Commanditaire
Colonne D : Date
(Le séparateur "_" peut changer si besoin.)

Pour information, je me débrouille en Excel mais je suis novice en VBA (je sais l'ouvrir, l'activer, l'exécuter mais absolument pas rédiger).

Voilà j'espère avoir été clair ! Je suppose qu'il n'y a pas besoin de joindre un fichier (je n'en ai pas de toute façon), mais je suivrai évidemment la discussion pour toute information supplémentaire !

Merci d'avance et merci encore !

 

[Robert]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

Bonjour Ripley et bienvenu, bonjour le forum,

J'en ai tellement marre de lire des premiers posts mal formulés et incompréhensibles que je suis tombé sur le cul en lisant le tien. Ça mérite des félicitations...
Mais ce n'est pas ce qui t'intéresse et je vais essayer de me pencher sur ton problème si j'ai un peu de temps ce soir. Pourrais-tu nous donner un petit fichier exemple reprenant la struture de ton original avec juste quelques données (de préférence en .xls pour les attardés qui, comme moi, utilisent encore office 2003).

 

[Ripley511]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

Merci beaucoup pour cette réponse rapide !

Ci-joint un modèle de ce que je pensais faire... Mais je n'ai inséré aucun code VBA ou macro... Je sais pas faire !

J'espère que ça te satisfait et merci encore !

 

[Robert]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

Bonsoir Ripley, bonsoir le froum,

En pièce jointe une proposition avec un CommanButton Mise à Jour... Le code est commenté pour que tu puisses mieux le comprendre.

Les impératif pour que ça plante pas sont :
• pas d'extension de plus de trois lettres (Indd, xlsm, etc)
• la date dans le nom du fichier toujours au format aaaammjj (pas 201211 pour le 1er janvier 2012 !)
• le format complet doit être : aaaammjj_Libellé_Commenditaire_Fournisseur.ext
• le séparateur doit toujours être "_" (tiret bas)
• ne pas réplacer les colonnes et laisser la colonne Date au format standard

Une fois le fichier listé il est renommé ! Les deux caractères "_L" sont rajoutés avant l'extension. Cela permet en rappuyant sur le bouton d'ignorer les fichier déjà listés. Attention ! Cela signifie que si on supprime une ligne du tableau le fichier correspondant ne sera pas actualisé à la prochaine mise à jour...

À l'ouverture du fichier, si il y a des nouveau fichiers dans la dossier, la mise à jour se fait automatiquement...
Le code se trouve dans deux endroits différents, le module Module 1 :

Public o As Object 'déclare la variable o (Onglet)
Public nl As Long 'déclare la variable nl

Sub Macro1()
Dim sf As Variant 'déclare la variable sf (Système de Fichiers)
Dim d As Variant 'déclare la variable d (Dossier)
Dim fs As Variant 'déclare la variable fs (Fichiers)
Dim f As Variant 'déclare la variable f (Fichier)
Dim nf As String 'déclare la variable nf (Nom du Fichier)
Dim ext As String 'déclare la variable ext (EXTension du Fichier)
Dim dc As String 'déclare la variable dc (Deux derniers Caractères du nom)
Dim dest As Range 'déclare la variable dest (cellule de DESTination)
Dim a As String 'déclare la variable a (Année)
Dim m As String 'déclare la variable m (Mois)
Dim j As String 'déclare la variable j (Jour)

Set sf = CreateObject("Scripting.FileSystemObject") 'définit la variable sf
Set d = sf.GetFolder("G:\1.6_Exploitation\Suivi budgétaire\Devis_reçus") 'définit le dossier d
'Set d = sf.GetFolder("C:\Test")
Set fs = d.Files 'définit l'ensemble des fichiers fs
'si le nombre de fichiers du dossier d est inférieur ou égal au nombre de ligne nl, sort de la procédure
'mise à jour automatique à l'ouverture du fichier (voir le code du composant ThisworkBook)
If fs.Count <= nl Then Exit Sub
For Each f In fs 'boucle sur tous les fichiers du dossier d
    nf = Left(f.Name, Len(f.Name) - 4) 'définit la nom du fichier (sans l'extension)
    ext = Right(f.Name, 3) 'définit l'extention du fichier
    dc = Right(nf, 2) 'définit les deux derniers caractères du nom du fichier
    If dc <> "_L" Then 'condition : si les deux derniers caractères du nom sonf différent de "_L" (fichier non listé)
        Set dest = o.Cells(Application.Rows.Count, 2).End(xlUp).Offset(1, 0) 'définit la cellule de destination
        For x = 0 To 3 'boucle sur les 4 séparations par le symbole "_"
            dest.Offset(0, x).Value = Split(nf, "_")(x) 'répercute chaque séparation dans la colonne
        Next x 'prochaine séparation de la boucle
        a = CStr(Left(dest.Value, 4)) 'définit l'année a
        m = CStr(Mid(dest.Value, 5, 2)) 'définit le mois m
        j = CStr(Right(dest.Value, 2)) 'définit le jour j
        dest.Value = a & "/" & m & "/" & j 'formate la date dans dest
        nf = nf & "_L" 'redéfinit le nom du fichier en ajoutant "_L" (_Listé)
        f.Name = nf & "." & ext 'renomme le fichier
    End If 'fin de la condition
Next f 'prochain fichier de la boucle
End Sub

et le composant ThisWorkbook :

Private Sub Workbook_Open()
Set o = Sheets("SUIVI DEVIS") 'définit l'onglet o
nl = o.Cells(Application.Rows.Count, 2).End(xlUp).Row - 3 'définit le nombre de lignes du tableau
Module1.Macro1 'lance la procédure "Macro1" du module "Module1"
End Sub

Le fichier :

 

[Ripley511]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

Génial !!! Merci Robert, je teste ça demain au boulot et je te tiens au courant du bon fonctionnement du code !

Je vais aussi essayer de décortiquer le code avec toutes les explications pour bien comprendre le langage VBA et, qui sait, peut-être que dans dix ans, je serai capable de répondre à un petit nouveau moi aussi

Bonne soirée et merci encore

Ripley

 

[Ripley511]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

Bonjour Robert,

Comme tu peux t'en douter, la première chose que j'ai faite en allumant l'ordi au boulot a été de tester cette macro !

J'ai deux soucis :

1) la date n'est pas correcte : il ne sépare pas l'année, le mois et le jour avec des "/" comme prévu dans le code. J'ai bien entré le format aaaammjj dans le nom du fichier, le format de la cellule est bien une date standard mais ça marche pas !
2) il y a un souci de débogage sur la ligne :
Set dest = o.Cells(Application.Rows.Count, 2).End(xlUp).Offset(1, 0) 'définit la cellule de destination
Avec l'erreur "Erreur d'exécution '91' - Variable objet ou variable de bloc non définie"

C'est grave docteur ?

En tout cas merci encore !
Ripley

 

[Robert]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

Bonjour Ripley, bonjour le forum,

Avant de t'envoyer j'avais testé... Dans un dossier test (dans la racine de C: ) j'avais un seul fichier : 20120101_Libellé1_Commenditaire1_Fournisseur1.txt.

Quand je lançais la macro, elle s'éxécutait sans problème... Je retrouvais dans le tableau :
• Colonne B : 2012/01/01
• Colonne C : Libellé1
• Colonne D : Commenditaire1
• Colonne E : Founisseur1
ensuite le fichier était renommé : 20120101_Libellé1_Commenditaire1_Fournisseur1_L.txt.

Évidemment si je faisais un nouveau test, il me fallait re-renommer le fichier en :20120101_Libellé1_Commenditaire1_Fournisseur1.txt (sans _L)pour que ça remarche...

Pour le premier problème de dates :
Attention il faut que la colonne date soit au format Standard et pas Date Standard

Pour le second problème de bug :
As-tu bien copié les deux lignes de déclaration des variables en haut du module :

Public o As Object 'déclare la variable o (Onglet)
Public nl As Long 'déclare la variable nl

Elle sont indispensables...

 

[Ripley511]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

OK c'est bon ça marche ! Merci beaucoup.

Juste une précision pour les personnes qui liraient ce post dans longtemps : le fichier Excel ne doit pas être dans le même répertoire que les fichiers listés (c'est probablement une évidence pour beaucoup d'entre vous...)

Merci encore Robert !
Ripley

 

[Robert]

Re : [VBA] Lister et répertorier le contenu d'un dossier Windows

Bonjour Ripley, bonjour le forum,

Content que ça fonctionne... Je me suis rendu compte que cette ligne était superflue tu peux la supprimer :

If fs.Count <= nl Then Exit Sub

Et dans la mesure où...

j'ai opté pour une solution simple :
- Placer tous les devis reçus dans un dossier "Devis_reçus"

C'était aussi évident pour moi !