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!!