FileSearch misbehaving

[Robin Clay]

Greetings !

I'm sure this USED to work, but now it doesn't !

Any ideas - Please ?
----------------------------------------
' Get the folder name from Cell A4
FolderName = Range("A4").Value
'Get all the filenames
Set fs = Application.FileSearch
With fs
.LookIn = FolderName 'Path$
.FileName = "*.*"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending)
= 0 Then
MsgBox "There were no files found."
Else
Select Case .FoundFiles.Count
Case Is = 0
MsgBox "No files were found. Sorry!"
Case Is = 1
MsgBox Chr(34) & .FoundFiles(1) & Chr(34) & " was the only
file found."
' Process .FoundFiles(1), I
Worksheets("Sheet1").Range("A12").Value = .FoundFiles(1)
Case Else
For N = 1 To .FoundFiles.Count
BoxText$ = BoxText$ & " " & N & ". " & .FoundFiles(N) &
vbCrLf
Worksheets("Sheet1").Range("A" & 11 + N).Value =
(FileNameOnly(.FoundFiles(N)))
'Worksheets(Sheet1).Range(Chr(65 + N) & 13 + N & ":" &
Chr(65 + N) & 13 + N + 80).Font.Color = QBColor(N)
Next N
' MsgBox ("These " & .FoundFiles.Count & " files
were found:-" & vbCrLf & BoxText$)
' Process .FoundFiles(N), I
End Select
End If
End With
Range("F4") = N & "files"
-------

It runs OK, but always returns "No files were found. Sorry!"


Regards

Robin

 

[Barb Reinhardt]

Are you using it with 2007?

http://support.microsoft.com/kb/935402

 

[Robin Clay]

Thanks, Barb, for responding.

ver. 2002 - SP3

 

[Don Guillett]

What is filenameonly?

Worksheets("Sheet1").Range("A" & 11 + N).Value = .FoundFiles(N)
instead of
'Worksheets("Sheet1").Range("A" & 11 + N).Value =
(FileNameOnly(.FoundFiles(N)))
=====
You may find this easier
Sub FindFilesA()
Application.ScreenUpdating = False
Dim FN As String
Dim ThisRow As Long
Dim FileLocation As String
FileLocation = "c:\a\*.*"
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[Robin Clay]

Oooppss ! Sorry.

-----------
Public Function FileNameOnly(PathAndFile$) As String
'Given e.g. "C:\My Documents\My Folder\MyFile.doc" this returns just
"MyFile.doc"
Dim path$, LastSlash As Integer, N As Integer

LastSlash = 0
N = 1
N = 1
Do
N = N + 1
If Left$(Right$(PathAndFile$, N), 1) = "\" Then LastSlash = N
Loop Until LastSlash > 1
FileNameOnly = Mid$(PathAndFile$, Len(PathAndFile$) - N + 2)
End Function
---------------

Thank you for your suggestion - I'll give it a whirl...


Regards

Robin

 

[Robin Clay]

Hello again, Don !

Alas! On my machine your routine falls over at the "Cells....." line


Regards

Robin

 

[Don Guillett]

Your excel version?
______________________

 

[Robin Clay]

Barb asked me that, see my reply.

Ver. 2002 SP3

 

[Don Guillett]

The macro works just fine on my xl 2003. If desired, send your workbook to
my addy below.
Did you use YOUR folder location?FileLocation = "c:\yourfoldernamehere\*.*"