K
KateB
Hoping someone can help me - it seems that Application.FileSearch no
longer works in Excel 2007. I used to have a routine (below) which
listed all the files in a directory (and possibly sub directory. I
had a dig around on the group last night & I think I need to use fso,
but I'm not exactly sure what that means, or how to implement it. Can
someone help me?
My knowledge of VBA is poor-moderate.
Thanks,
Kate
Sub ListFiles()
Set fs = Application.FileSearch
With fs
.LookIn = ActiveWorkbook.Path
.SearchSubFolders = True
.FileName = "*.*"
If .Execute() > 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found."
'For I = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(I)
'Next I
Else
MsgBox "There were no files found."
End If
'write headings
Range("A1").Value = "Size (MB)"
Range("B1").Value = "File"
Range("C1").Value = "Date"
For i = 1 To .FoundFiles.Count
Range("B" & i + 1).Value = .FoundFiles(i)
Range("A" & i + 1).Value = FileLen(.FoundFiles(i)) / 1024 ^ 2
Range("C" & i + 1).Value = FileDateTime(.FoundFiles(i))
Next
End With
End Sub
longer works in Excel 2007. I used to have a routine (below) which
listed all the files in a directory (and possibly sub directory. I
had a dig around on the group last night & I think I need to use fso,
but I'm not exactly sure what that means, or how to implement it. Can
someone help me?
My knowledge of VBA is poor-moderate.
Thanks,
Kate
Sub ListFiles()
Set fs = Application.FileSearch
With fs
.LookIn = ActiveWorkbook.Path
.SearchSubFolders = True
.FileName = "*.*"
If .Execute() > 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found."
'For I = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(I)
'Next I
Else
MsgBox "There were no files found."
End If
'write headings
Range("A1").Value = "Size (MB)"
Range("B1").Value = "File"
Range("C1").Value = "Date"
For i = 1 To .FoundFiles.Count
Range("B" & i + 1).Value = .FoundFiles(i)
Range("A" & i + 1).Value = FileLen(.FoundFiles(i)) / 1024 ^ 2
Range("C" & i + 1).Value = FileDateTime(.FoundFiles(i))
Next
End With
End Sub