Solved

Excel Macro - List Files and Subfolders with details

Posted on 2010-09-24
3
965 Views
Last Modified: 2012-05-10
Hi guys,

I need make a Macro to list in Excel all content of a folder (files, subfolders etc) with details example size and datetime.

Thanks!
0
Comment
Question by:Gonzalo Becerra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33753620
Try this - just modify the path and output range in the first sub as required:



Sub TestListFoldersAndFiles()
   FolderListing "C:\test", Range("A1")
End Sub
Sub FolderListing(strFilepath As String, ByRef rngOutput As Range)
   Dim fso As Object, fdrSelected, fdrSub, filTemp
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fdrSelected = fso.GetFolder(strFilepath)
   With rngOutput
      .Value = fdrSelected.Path & " (" & fdrSelected.Files.Count & ")"
      .Font.Bold = True
   End With
   For Each filTemp In fdrSelected.Files
      Set rngOutput = rngOutput.Offset(1)
      rngOutput.Value = filTemp.Name
      rngOutput.Offset(, 1).Value = filTemp.Size
      rngOutput.Offset(, 2).Value = filTemp.DateLastModified
   Next filTemp
   Set rngOutput = rngOutput.Offset(2)
   For Each fdrSub In fdrSelected.SubFolders
      FolderListing fdrSub.Path, rngOutput
   Next fdrSub
   Set fso = Nothing

End Sub

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33754024
gbecerra,Glad to see you got an answer!You might also want to have a look at this PAQ:http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25063189.html#a26340696In that comment, I have code that will enumerate the files in a directory, but allow you at runtime to browse to find the directory you want enumerated, and also allow you to indicate whether or not to traverse the subfolders and descendants.Patrick
0
 
LVL 1

Author Comment

by:Gonzalo Becerra
ID: 33754598
Thanks alot!! i check the two solutions and help me alot!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question