VBA - List Files and Folders

hennanra

New Member
Joined
Jan 10, 2006
Messages
16
Hello,

I am trying to find some completed excel VBA code or a macro that allows me to specify a directory and automatically generates a list in Excel of the folders, subfolders and files - i.e. a full directory tree. I don't wish to use the DOS command for a few reasons. Please can someone advise if they know of any such code or if they have something to hand (don't have time to modify unfortunately so looking for something complete)

Kind Regards,
Raheel
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Have two sheets in your workbook, one called "Listing" and one called "Control", in the control sheet in cell B2 copy in the path you wish to list, and then run the sub below. Also name the cell B2 with a range name of "Directory".

Code:
Sub DirList()


Dim fs, d, ff, fx

Dim iRow As Integer

  Sheets("Listing").Select
  Cells.Select
  Selection.ClearContents
  Range("A1").Select

  Cells(1, 1) = "Directory listing of " & Range("Directory")
  Cells(2, 1) = Now

  Cells(4, 1).Formula = "Name"
  Cells(4, 2).Formula = "Size"
  Cells(4, 3).Formula = "Created"
  Cells(4, 4).Formula = "Last accessed"
  Cells(4, 5).Formula = "Last modified"
  
  iRow = 5
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set d = fs.getfolder(Range("Directory"))
  
  Set ff = d.Subfolders
  For Each fx In ff
    Cells(iRow, 1).Formula = fx.Name
    Cells(iRow, 2).Formula = "<DIR>"
    Cells(iRow, 3).Formula = fx.datecreated
    Cells(iRow, 4).Formula = fx.datelastaccessed
    Cells(iRow, 5).Formula = fx.datelastmodified
    iRow = iRow + 1
  Next fx
  
  iRow = iRow + 1
  
  Set ff = d.Files
  For Each fx In ff
    Cells(iRow, 1).Formula = fx.Name
    Cells(iRow, 2).Formula = fx.Size
    Cells(iRow, 3).Formula = fx.datecreated
    Cells(iRow, 4).Formula = fx.datelastaccessed
    Cells(iRow, 5).Formula = fx.datelastmodified
    iRow = iRow + 1
  Next fx
  
End Sub
 
Upvote 0
Look up the FileSystem Object. This will allow you to access the file system of windows and allows you to create directories, files etc. Be careful though, you can do a lot of damage with it. You would set up a recursive style loop that grabs file attributes from files and subfolder files and prints to the worksheet A good start link is;

http://msdn2.microsoft.com/en-us/library/y32kbeb6(VS.80).aspx
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top