Solved: Method "FileSearch" Problem

[YasserKhalil]

I have a problem in the following file
There's a debug during the execution of the code
in the FileSearch method

[mdmackillop]

Your zip file contained an .exe file. Please only include Excel, Word, text files etc. in any attachment.

[GTO]

Greetings Yasser,

Read here: http://support.microsoft.com/kb/920229/en-us

...but Application.FileSearch is not supported in excel 2007.

There are workarounds of course.

[YasserKhalil]

Thanks for guiding me to the right way
But Can any changes made to the file to do the purpose of it
I want to have a list of files that are in the directory C:\ in Column A for example

[GTO]

Hi Yasser,

Do you want it just to list each file's full name just like you have .FileSearch doing presently?

Mark

[YasserKhalil]

Yes. my dear Mark

[Bob Phillips]

Look at Dir in VBA help.

[GTO]

Just another way using FileSystemObject (FSO). I do not often use the FolderPicker, but seem to have it okay.

For the button's code in the Worksheet Module:

Option Explicit
    
Private Sub CommandButton1_Click()
Dim _
aryFileNames    As Variant, _
rngTopCell      As Range
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        
        .AllowMultiSelect = False
        .ButtonName = "Run List"
        If .Show = -1 Then
            aryFileNames = _
                FileList_Ret(.SelectedItems(1) & Application.PathSeparator)
            
            On Error Resume Next
            Set rngTopCell = _
                Application.InputBox(Prompt:="Select the upper cell to start the list in", _
                                     Title:="", _
                                     Type:=8)
            On Error GoTo 0
            
            If rngTopCell Is Nothing Then Exit Sub
            
            rngTopCell(1).Resize(UBound(aryFileNames, 1)).Value = aryFileNames
            rngTopCell.EntireColumn.AutoFit
        Else
            Exit Sub
        End If
    End With
End Sub

In a Standard Module:

Option Explicit
    
Function FileList_Ret(SelectedFolder As String)
Dim _
FSO         As Object, _
fsoFolder   As Object, _
fsoFile     As Object, _
aryFileList As Variant, _
i           As Long
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = FSO.GetFolder(SelectedFolder)
        
    ReDim aryFileList(1 To fsoFolder.Files.Count, 1 To 1)
    
    For Each fsoFile In fsoFolder.Files
        i = i + 1
        aryFileList(i, 1) = fsoFile.Path
    Next
    FileList_Ret = aryFileList
End Function

Hope that helps,

Mark

[YasserKhalil]

Dear Mark
Thanks a lot for your help
Another simple request for you
Just a bit explaining of the code as I want to learn more!!
If you haven't a plenty of time, I won't be sad..I'm glad of your solution
Thanks again

[GTO]

Hopefully this will be a little help at least :-)

Function FileList_Ret(SelectedFolder As String)
Dim _
FSO         As Object, _
fsoFolder   As Object, _
fsoFile     As Object, _
aryFileList As Variant, _
i           As Long
    
    '// Set a reference to FileSystemObject Object, and to the folder we picked.        //
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = FSO.GetFolder(SelectedFolder)
    
    '// Resize our array to a two dimensional array, so that we can just plunk the      //
    '// values onto a sheet.                                                            //
    ReDim aryFileList(1 To fsoFolder.Files.Count, 1 To 1)
    
    For Each fsoFile In fsoFolder.Files
        i = i + 1
        aryFileList(i, 1) = fsoFile.Path
    Next
    FileList_Ret = aryFileList
End Function

Private Sub CommandButton1_Click()
Dim _
aryFileNames    As Variant, _
rngTopCell      As Range
    
    '// Reference the .FileDialog object, read thru the various properties and such in  //
    '// Help, as I would be no better in explaining.  Here, we just ensure that the     //
    '// user can pick only one folder (which I'm not actually sure whether you can      //
    '// multi-select folders), and we chek to see if user selected a folder to run      //
    '// against with .Show= -1                                                          //
    With Application.FileDialog(msoFileDialogFolderPicker)
        
        .AllowMultiSelect = False
        .ButtonName = "Run List"
        If .Show = -1 Then
            
            '// Plunk the array of file fullnames returned from the function FileList_Ret//
            '// Note that although .SelectedItems is a collection, we can safely pick the//
            '// first element, as we only allowed one folder to be picked.              //
            aryFileNames = _
                FileList_Ret(.SelectedItems(1) & Application.PathSeparator)
            
            '// Skip past error if user cancels box w/o picking a range.                //
            On Error Resume Next
            Set rngTopCell = _
                Application.InputBox(Prompt:="Select the upper cell to start the list in", _
                                     Title:="", _
                                     Type:=8)
            On Error GoTo 0
            
            '// If user DID cancel inputbox, then rngTopCell was never Set a reference  //
            '// to, and does not exist.  Thus, if it Is Nothing, then we want to bail.  //
            If rngTopCell Is Nothing Then Exit Sub
            
            '// Basically, since the user may have inadvertantly selected more than one //
            '// row and/or column in the inputbox, we resize from the first cell of the //
            '// range.
            rngTopCell(1).Resize(UBound(aryFileNames, 1)).Value = aryFileNames
            rngTopCell.EntireColumn.AutoFit
        Else
            Exit Sub
        End If
    End With
End Sub

Have a great day,

Mark

[YasserKhalil]

That's really a great day as I know you!!