Application.FileSearch in Excel 2007

[Barmoley]

My below function stopped working in Excel 2007, what should I change in the function to match it to Excel 2007?

Function IfEmpty(ByVal myPath As String) As Boolean

    '   function returns True/False if directory is empty

    With Application.FileSearch
        .LookIn = myPath
        .FileName = "*.*"
        .SearchSubFolders = True
        If .Execute(msoSortByFileName, msoSortOrderAscending) > 0 Then
            IfEmpty = False
        Else
            IfEmpty = True
        End If
    End With
End Function

[pike]

Barmoley
try..

Set fs = Application.FileSearch
With fs
    .LookIn = mypath
    .FileName = "*.*"
    If .Execute > 0 Then
IfEmpty= True
    Else
      IfEmpty= False
    End If

[Barmoley]

As I told "Application.FileSearch" does not exist in Excel 2007

[pike]

bummer
try this workbook

Option Explicit

Sub File_Documentation()
    
    'Search criteria which includes all Excel file types.
    Const sSEARCH_CRITERIA As String = "*.xl*"
    
    'Variables for the Excel objects.
    Dim rngFileNames As Range
    Dim rngFileCount As Range
    Dim lRowCount As Long
    
    'Variable for the path string.
    Dim sFilePath As String
    
    'Variable to indicate if subfolders
    'also be part of the search.
    Dim bSearchSubFolders As Boolean
    
    'Variable for the Dictionary object.
    Dim dctFileNames As Scripting.Dictionary
    
    'Variable to be used when we iterate through
    'the Dictionary collection.
    Dim vFileName As Variant
    
    Set rngFileCount = wksDocumentation.Range("C6")
    Set rngFileNames = wksDocumentation.Range("E4")
    
    'Get the path which can be chosen from a list
    'in the cell.
    sFilePath = wksDocumentation.Range("C4").Value
    
    'Get the boolean value to decide to include or
    'to exclude the subfolders. It can be left
    'empty.
    bSearchSubFolders = wksDocumentation.Range("C5").Value
    
    'Instantiate a new Dictionary collection object.
    Set dctFileNames = New Scripting.Dictionary
    
    lRowCount = 1
    
    Application.ScreenUpdating = False
    
    'Get the file names.
    If bGet_Excel_Files(sFilePath, dctFileNames, _
                       sSEARCH_CRITERIA, _
                       bSearchSubFolders) Then
        
        'Retrieve the number of located files.
        rngFileCount.Value = dctFileNames.Count

        'Iterate the Dictionary collection.
        For Each vFileName In dctFileNames
            
            'Write the retrieved file names into the worksheet.
            rngFileNames.Offset(lRowCount, 0).Value = vFileName
            
            lRowCount = lRowCount + 1
        
        Next vFileName
    
    End If
    
    wksDocumentation.Columns("E:E").EntireColumn.AutoFit
    
    'Release object from the memory.
    Set dctFileNames = Nothing
    
End Sub

Private Function bGet_Excel_Files( _
                 ByVal sPath As String, _
                 ByRef dctDictionary As Scripting.Dictionary, _
                 ByVal sSearchCriteria As String, _
                 Optional ByVal bRecursive As Boolean) As Boolean
                                 
    Dim fsoFileSystem As Scripting.FileSystemObject
    Dim fsoFolder As Scripting.Folder
    Dim fsoSubFolder As Scripting.Folder
    Dim fsoFile As Scripting.File
    
    'Instantiate a new FSO object.
    Set fsoFileSystem = New Scripting.FileSystemObject
    
    'Check to see if the folder exist or not.
    If fsoFileSystem.FolderExists(sPath) Then
    
        Set fsoFolder = fsoFileSystem.GetFolder(sPath)
    
    Else
        
        MsgBox "The folder: " & vbNewLine & _
               sPath & vbNewLine & _
               "does not exist.", vbCritical
        
        bGet_Excel_Files = False
        
        GoTo ExitFunction
    
    End If
    
    'Iterate through the files in the folder and add the files
    'that meet the search criteria.
    For Each fsoFile In fsoFolder.Files
    
        If fsoFile.Name Like sSearchCriteria Then
            
            'Add the file name to the Dictionary.
            dctDictionary.Add Key:=fsoFile.Name, Item:="File List"
               
        End If
        
    Next fsoFile
    
    If bRecursive Then
        
        'The function is called recursively to return the file
        'names in each subfolder.
        For Each fsoSubFolder In fsoFolder.SubFolders
            bGet_Excel_Files fsoSubFolder.Path, _
                             dctDictionary, _
                             sSearchCriteria, _
                             bRecursive:=True
        Next fsoSubFolder
    
    End If
    
    bGet_Excel_Files = True
        
ExitFunction:

    'Release objects from the memory.
    Set fsoFile = Nothing
    Set fsoSubFolder = Nothing
    Set fsoFolder = Nothing
    Set fsoFileSystem = Nothing

End Function

[Barmoley]

The code looks as a monster, what should I do with the range and worksheets?
I am looking for solution to give as an argument "myPath" and to return boolean True/False.

[pike]

Hi Barmoley
try this

Private Function IfEmptyTest(mypath As String) As Boolean
    Dim fsoFileSystem As Scripting.FileSystemObject
    Dim bRecursive As Boolean
    Dim dctFileNames As Scripting.Dictionary
    Set dctFileNames = New Scripting.Dictionary
    sSearchCriteria = "*.xl*"
    bRecursive = True
    'myPath = "C:\Documents and Settings\User\My Documents\New Folder Examples\pike"
    Set fsoFileSystem = New Scripting.FileSystemObject
     If fsoFileSystem.FolderExists(mypath) Then
         Set fsoFolder = fsoFileSystem.GetFolder(mypath)
        For Each fsoFile In fsoFolder.Files
            Debug.Print "List " & fsoFile.Name
        If fsoFile.Name Like sSearchCriteria Then
           IfEmptyTest = True
            Debug.Print "Added " & fsoFile.Name
         ' dctFileNames.Add Key:=fsoFile.Name, Item:="File List"
 
          End If
          Next fsoFile
 ' For Each fsoSubFolder In fsoFolder.SubFolders
  '    IfEmptyTest fsoSubFolder.Path
 '    Next fsoSubFolder
    Else
    IfEmptyTest = False
    End If
 End Function

[royUK]

See this

http://excel2007tips.blogspot.com/