list all excel files in a folder

[lior03]

hello
i want to create a report of all excel file in a certain folder.i built a udf for a filesize and a file last save information.
how can i "drag" the formulas along the report .what is wrong with the folder i specidied?

Sub listfiledoctor()
Dim MyRow As Integer
Dim myfile As String
Dim directory As String
Sheets.Add
Cells(1, 1) = "filename"
Cells(1, 2) = "date last save"
Cells(1, 3) = "filesize"
MyRow = 2
directory = "F:\Documents and Settings\Moshe\My Documents"
myfile = Dir(directory, "*.xls")
Do Until myfile = ""
Cells(MyRow, 1) = myfile
MyRow = MyRow + 1
myfile = Dir
ActiveSheet.name = "files1"
Loop
With Range("B2").Select
ActiveCell.FormulaR1C1 = "=filelastsave(RC[-1])"
End With
With Range("C2").Select
ActiveCell.FormulaR1C1 = "=filesize(RC[-2])"
End With
End Sub


thanks

[Dr.K]

First off, I think you are missing a "\", and the Dir syntax look wrong.
Try this:

Path = "F:\Documents and Settings\Moshe\My Documents\"
MyFile = Dir(Path & "*.xls")


Second, why would you need to use worksheet functions for that? It would be MUCH cleaner if you used FSO. I would Dir through the file list, and then pull the data for each file with FSO (from inside the loop).

As for mass applying a R1C1 style function, just set the entire range = to the formula, like this:

Range("B2:B" & Cells(Rows.count, 1).End(xlUp).Row).FormulaR1C1 = "=filelastsave(RC[-1])"
Range("C2:C" & Cells(Rows.count, 1).End(xlUp).Row).FormulaR1C1 = "=filesize(RC[-2])"