|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com