Excel VBA: Application.FileSearch Error [Solved/Closed]

Report
Posts
1
Registration date
Monday June 10, 2013
Status
Member
Last seen
June 10, 2013
-
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
-
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.

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014

Sorry for the late reply.

Thanks, rizvisa1. This problem is resolved now.