lior03
10-08-2007, 11:32 AM
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
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