View Full Version : Solved: Method "FileSearch" Problem
YasserKhalil
06-18-2010, 12:02 AM
I have a problem in the following file
There's a debug during the execution of the code
in the FileSearch method
mdmackillop
06-18-2010, 12:31 AM
Your zip file contained an .exe file. Please only include Excel, Word, text files etc. in any attachment.
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
06-18-2010, 01:08 AM
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
Hi Yasser,
Do you want it just to list each file's full name just like you have .FileSearch doing presently?
Mark
YasserKhalil
06-18-2010, 01:16 AM
Yes. my dear Mark
Bob Phillips
06-18-2010, 01:19 AM
Look at Dir in VBA help.
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
06-18-2010, 04:03 AM
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
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
06-18-2010, 10:10 AM
That's really a great day as I know you!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.