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

w_l 1 Posts Monday June 10, 2013Registration date June 10, 2013 Last seen - Jun 10, 2013 at 03:31 AM - Latest reply: wliang 41 Posts Thursday June 16, 2011Registration date May 7, 2014 Last seen
- 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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 16, 2013 at 10:13 AM
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

Thank you, rizvisa1 4

Something to say? Add comment

CCM has helped 1869 users this month

wliang 41 Posts Thursday June 16, 2011Registration date May 7, 2014 Last seen - Oct 8, 2013 at 04:39 AM
0
Thank you
Sorry for the late reply.

Thanks, rizvisa1. This problem is resolved now.