Results 1 to 7 of 7

application.filesearch not working in excel 2007

Threaded View

  1. #1
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    application.filesearch not working in excel 2007

    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
    Last edited by johnjohns; 12-21-2009 at 05:24 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1