I wrote a macro to list all the excel files from a directory and its sub-directories to an excel sheet. It is working in excel 2003 but shows error (object does not support this action) in 2007. Actually I have copied almost this entire macro from excel 2007 help only. Can somebody modify this to use in both the versions of excel?
Sub GetAllDirectories()
Dim IncludeSubDirectories As Integer
Directory = GetDirectory(Msg)
IncludeSubDirectories = MsgBox("Include Sub-Directories also? ", vbYesNo)
ActiveSheet.Cells.Select
Selection.Delete Shift:=xlUp
Range("B2").Select
Set fs = Application.FileSearch
With fs
.LookIn = Directory
.SearchSubFolders = IIf(IncludeSubDirectories = 6, True, False)
.Filename = "*.xls;*.xlsm;*.xlsx"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
ActiveCell.Offset(i, 0).Value = Left(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") - 1)
ActiveCell.Offset(i, 1).Value = Right(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
ActiveCell.Offset(i, 2).Value = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
Error occurs at the line
Set fs = Application.FileSearch
Bookmarks