Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > FileSearch misbehaving
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 40
Default FileSearch misbehaving
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default FileSearch misbehaving
Are you using it with 2007?

http://support.microsoft.com/kb/935402
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Robin Clay" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 40
Default FileSearch misbehaving
Thanks, Barb, for responding.

ver. 2002 - SP3
--
Regards

Robin


"Barb Reinhardt" wrote:

Are you using it with 2007?

http://support.microsoft.com/kb/935402
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Robin Clay" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default FileSearch misbehaving
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 40
Default FileSearch misbehaving
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


"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 40
Default FileSearch misbehaving
Hello again, Don !

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


Regards

Robin


"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default FileSearch misbehaving

Your excel version?
______________________
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
Hello again, Don !

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


Regards

Robin


"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 40
Default FileSearch misbehaving
Barb asked me that, see my reply.

Ver. 2002 SP3

--
Regards

Robin


"Don Guillett" wrote:


Your excel version?
______________________
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
Hello again, Don !

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


Regards

Robin


"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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




Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default FileSearch misbehaving
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:\a\*.*"
FileLocation = "c:\yourfoldernamehere\*.*"


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
Barb asked me that, see my reply.

Ver. 2002 SP3

--
Regards

Robin


"Don Guillett" wrote:


Your excel version?
______________________
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
Hello again, Don !

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


Regards

Robin


"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Misbehaving Tab cynlee Excel Worksheet Functions 1 April 3rd 08 05:21 PM
AutoFilter May be Misbehaving Marsh New Users to Excel 7 November 22nd 06 01:48 PM
Workbook Misbehaving bkworks Excel Discussion (Misc queries) 2 August 8th 06 04:09 PM
excel misbehaving R..VENKATARAMAN Excel Discussion (Misc queries) 2 February 19th 06 02:52 AM
Misbehaving 'Worksheet_Open()' Event James[_14_] Excel Programming 4 December 2nd 03 09:19 PM


All times are GMT +1. The time now is 10:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement