Previous Topic Next Topic

Excel VBA: Application.FileSearch Error

Solved/Closed
w_l Posts 1 Registration date Monday June 10, 2013 Status Member Last seen June 10, 2013 - Jun 10, 2013 at 03:31 AM
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Oct 8, 2013 at 04:39 AM
Hello everyone,

I encountered error "Object doesn't support this action." when the program the line "With Application.FileSearch". Here is my codes.

*****
Public Function scrapnewest() As Variant
Dim V(1 To 2) As Variant
Dim fldrpath As String
Dim newest0 As String

With ThisWorkbook

Yr = Right(Year(Now), 2)

Yrp1 = Yr + 1
Yrm1 = Yr - 1

If Len(Yrp1) = 1 Then
Yrp1 = "0" & Yrp1
ElseIf Len(Yrm1) = 1 Then
Yrm1 = "0" & Yrm1

End If


If Len(Dir(.Sheets("BasicData").Cells(5, 2).Value & "\" & Yr & "_" & Yrp1, vbDirectory)) > 0 Then
fldrpath = .Sheets("BasicData").Cells(5, 2).Value & "\" & Yr & "_" & Yrp1
V(2) = Yr & Yrp1
ElseIf Len(Dir(.Sheets("BasicData").Cells(5, 2).Value & "\" & Yrm1 & "_" & Yr, vbDirectory)) > 0 Then
fldrpath = .Sheets("BasicData").Cells(5, 2).Value & "\" & Yrm1 & "_" & Yr
V(2) = Yrm1 & Yr
End If

With Application.FileSearch
.NewSearch
.LookIn = fldrpath
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = False

If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count 'foundfiles with ending .xls
If Left(Right(.FoundFiles.Item(i), 8), 4) > Left(Right(newest0, 8), 4) Then
newest0 = .FoundFiles.Item(i)
End If
Next i
End If


V(1) = newest0
V(2) = Yr
scrapnewest = V

End With
End With

End Function
*****

Appreciate the help from everyone. Thank you very much.

Best regards.
Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 16, 2013 at 10:13 AM
It is saying there is no such thing in "Application" object called "FileSearch" I am guessing it is 2003 or older macro that you are trying to run to 2007 and above

You can change the code to DIR or use FileSystemObject

DISCLAIMER : ******* NOT TESTED ****************
you can try this 

      Dim fileName As String
      fileName = Dir(fldrpath & "\" & "*.xls")
      Do While fileName <> vbNullString
      
         If Left(Right(fileName, 8), 4) > Left(Right(newest0, 8), 4) Then
            newest0 = fileName
         End If
         fileName = Dir
      Loop
      V(1) = newest0
      V(2) = Yr
      scrapnewest = V


in place of 
      With Application.FileSearch
         .NewSearch
         .LookIn = fldrpath
         .SearchSubFolders = False
         .fileName = "*.xls"
         .MatchTextExactly = False
         
         If .Execute() > 0 Then
            For I = 1 To .FoundFiles.Count 'foundfiles with ending .xls
               If Left(Right(.FoundFiles.Item(i), 8), 4) > Left(Right(newest0, 8), 4) Then
                  newest0 = .FoundFiles.Item(i)
               End If
            Next i
         End If
         
         V(1) = newest0
         V(2) = Yr
         scrapnewest = V
      End With
4
Answer 2 / 2
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Oct 8, 2013 at 04:39 AM
Sorry for the late reply.

Thanks, rizvisa1. This problem is resolved now.
0