Advanced Search

[vba-excel] lister fichiers avec détails, dans une feuille excel

/ Published in: Visual Basic
Save to your folder(s)
How to list files, with details, in an excel sheet

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Option Explicit
  2. Public Chemin As String, I As Long
  3. Sub RepFichiers()
  4. Dim objShell As Object, objFolder As Object
  5. Set objShell = CreateObject("Shell.Application")
  6. Set objFolder = objShell.BrowseForFolder(&H0&, "Choisisser un répertoire", &H1&)
  7. On Error Resume Next
  8. Chemin = objFolder.ParentFolder.ParseName(objFolder.Title).Path & ""
  9. On Error GoTo 0
  10. If Chemin = "" Then Exit Sub
  11. Application.ScreenUpdating = False
  12. With Sheets("ACCUEIL")
  13. .Range("B12") = Chemin
  14. .Range("B16:E10000").ClearContents
  15. End With
  16. I = 16
  17. ListeFichier (Chemin)
  18. End Sub
  19. Function ListeFichier(Chemin As String) As String
  20. Dim Dossier As Object, SousDossier As Object, Fichier As Object
  21. Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin)
  22. With Sheets("ACCUEIL")
  23. For Each SousDossier In Dossier.SubFolders
  24.     .Cells(I, 2) = SousDossier.Name
  25.     For Each Fichier In SousDossier.Files
  26.     .Cells(I, 3) = Left(Fichier.Name, InStr(Fichier.Name, ".") - 1) 'Nom du fichier avec l'extension
  27.     .Cells(I, 4) = Dossier & "\" & SousDossier & "\" & Fichier.Name
  28.  
  29.     .Cells(I, 5) = Fichier.DateCreated  ' Date de création
  30.     .Cells(I, 6) = Fichier.DateLastModified  ' dernière modification
  31. ActiveSheet.Hyperlinks.Add Anchor:=Cells(I, 4), Address:=SousDossier & "\" & Fichier.Name
  32.     I = I + 1
  33.      Next
  34. Next
  35. End With
  36. End Function

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.