Here are two more versions of the macro. The first takes the directory in
cell A1 of the active sheet and gets all the files. The 2nd does everything.
You enter a directory. the code creates a new worksheet for every
sub-directory and put the files names on each sheet.


Sub getdates2()
folder = ActiveSheet.Range("A1")
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(folder)

RowNumber = 2
'folder size in bytes
On Error GoTo 200
For Each fl In folder.files
ActiveSheet.Cells(RowNumber, "C") = fl.DateLastModified
ActiveSheet.Cells(RowNumber, "B") = fl.Size
ActiveSheet.Cells(RowNumber, "A") = strFolder & fl.name
RowNumber = RowNumber + 1
Next fl

200 On Error GoTo 0
End Sub


Sub getdates3()
folder = "H:\My Documents"
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(folder)

'folder size in bytes
On Error GoTo 200


For Each sf In folder.subfolders
Set newsht = Worksheets.Add(after:=Sheets(Sheets.Count))

For Each fl In sf.files
Set newsht = Worksheets.Add(after:=Sheets(Sheets.Count))
newsht.Range("A1") = fl
RowNumber = 2
newsht.Cells(RowNumber, "C") = fl.DateLastModified
newsht.Cells(RowNumber, "B") = fl.Size
newsht.Cells(RowNumber, "A") = fl.name
newsht.name = sf.name
RowNumber = RowNumber + 1
Next fl
Next sf

200 On Error GoTo 0
End Sub


"driller" wrote:

Dear All,

i have this basic macro provide by forum member that can do a lot of work.
but due to more demands, i like that this macro can be separately operated
made for each sheets in one workbook.
In a way that when i place in A1 the text for Target Directory, the macro
will react and give result on the opened sheet starting from Row 2.
Same will go for other sheets in one workbook. I have 50 Folders that
contains a 100 different files, and these files are oftenly increased,
replaced or deleted.
Thats why I need this Macro very badly to produce a working comparative
workbook in a weekly bloody basis.

here below is the nice macro

Sub getdates()
Folder = "D:\My Documents\FOLDER-1"
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set Folder = _
fso.GetFolder(Folder)

RowNumber = 1
'folder size in bytes
On Error GoTo 200
For Each fl In Folder.Files
Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified
Sheets(1).Cells(RowNumber, "B") = fl.Size
Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name
RowNumber = RowNumber + 1
Next fl

200 On Error GoTo 0

Im not good in reading between the lines of this language...please help.
Thanks in advance.

--
best regards,