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

Posts
1
Registration date
Monday June 10, 2013
Last seen
June 10, 2013
- - Latest reply: wliang
Posts
41
Registration date
Thursday June 16, 2011
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.
See more 

2 replies

Best answer
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
915
4
Thank you
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

Say "Thank you" 4

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

CCM 4573 users have said thank you to us this month

Posts
41
Registration date
Thursday June 16, 2011
Last seen
May 7, 2014
0
Thank you
Sorry for the late reply.

Thanks, rizvisa1. This problem is resolved now.