VBA RunTime Error 445 - Application.FileSearch

Question

I have the same problem as the friend on this link: http://www.vbforums.com/showthread.php?503199-RESOLVED-Opening-an-excel-file-in-VB-without-the-exact-name&highlight=open%20file%20excel

Basically I would like to open a file, I know only a part of the file name, using VBA-Coding.

I found the webstie above with the potential solution but unfortunately, my compiler gives me Runtime Error 445

Sub openfile()
    Dim i As Integer
    With Application.FileSearch
        'Change the path to your path
        .LookIn = "C:\Temp"
        '* represents wildcard characters
        .FileName = "Sales_Report_1_4_2008*.xls"
        If .Execute > 0 Then 'Workbook exists
            'open all files that find the match
            For i = 1 To .FoundFiles.Count
                Workbooks.Open (.FoundFiles(i))
            Next i
        End If
    End With
End Sub

Could anyone help me to make this code work on Excek 2016??

Thanks a lot guys

Answer 1

I think FileSearch is discontinued. May use File system Object. May add reference to "Microsoft Scripting Runtime" and try

Sub openfile()
    Dim Path As String
    Dim FSO As FileSystemObject
    Dim Fl  As File
    Dim Fld As Folder

    Path = "C:\temp\"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Fld = oFSO.GetFolder(strPath)

    For Each Fl In Fld.Files
      If Ucase(Fl.Name) Like Ucase("Sales_Report_1_4_2008*.xls") Then
        Workbooks.Open (Fl.Path)
      End If
    Next Fl

    Set FSO = Nothing
    Set Fl = Nothing
    Set Fld = Nothing
End Sub

or even simpler loop through with Dir function

    Sub openfile()
        Dim Path As String
        Dim Fname As String

        Path = "C:\temp\"
        Fname = Dir(Path & "Sales_Report_1_4_2008*.xls")
        Do While Fname <> ""
           Workbooks.Open (Path & Fname)
        Fname = Dir
        Loop

End Sub