Set fs = Application.FileSearch doesn't work in newer versions of Excel

[vlvanriper]

I am converting an existing MS Excel spreadsheet from Office XP to Office 2016. When running the VBA code I get an error.

Run-time error '445':
Object doesn't support this action

The debug windows highlights this part of the code: Set fs = Application.FileSearch

I changed that part of the vba code to BOLD.

In researching this error I see that File Search is no longer supported in newer versions of Excel. I didn't originally create this spreadsheet and VBA code so I am not quite sure how to fix this issue.

I really would appreciate any and all help anyone could provide me.

--------------------------------------------------------------
This is my VBA Code:

Sub GetFilesList()
Dim t, s, FolderPath, FileName, Test(5), SingleChar As String
Dim i, j, v, Col, PathLen, PathAndFileLen, FileLen, Count, TestsIndex, TestersIndex As Integer


'clear files table area
Range("B8:K19").Select
Selection.ClearContents
Range("J21").Select

FolderPath = Worksheets("setpath").Cells(5, 3)
PathLen = Len(FolderPath)
Test(0) = "t"
Test(1) = "e"
Test(2) = "s"
Test(3) = "t"
Test(4) = "s"
TestsIndex = -1: TestersIndex = -1
Dim fs As Variable

Set fs = Application.FileSearch
With fs
    .LookIn = Worksheets("setpath").Cells(5, 3)
    .SearchSubFolders = False
    .FileName = "*.csv"
    If .Execute() > 0 Then
        For i = 1 To .FoundFiles.Count 'each file
            s = .FoundFiles(i)
            Count = 0
            PathAndFileLen = Len(s)
            FileLen = PathAndFileLen - PathLen + 1
            FileName = Right(s, FileLen)
            For j = 1 To FileLen
                SingleChar = LCase(Right(Left(FileName, j), 1))
                If SingleChar = Test(Count) Then
                    If Count = 3 Then
                        If LCase(Right(Left(FileName, j + 1), 1)) = "s" Then 'this is a tests file
                            TestsIndex = TestsIndex + 1
                            FilesTests(TestsIndex) = FileName
                        Else
                            TestersIndex = TestersIndex + 1
                            FilesTesters(TestersIndex) = FileName
                        End If
                        Exit For
                    End If
                    Count = Count + 1
                Else
                    Count = 0
                End If
            Next j
         Next i
    End If
End With
If TestsIndex <> TestersIndex Then 'not equal amounts of each file type
    MsgBox "FYI:  there are not equal numbers of each file type (Testers and Tests)."
End If
If TestsIndex < 0 Or TestersIndex < 0 Then 'must have at least one of each file type
    MsgBox "You must have at least one of each file type to compile data."
End If
For i = 8 To 19 'put file names in the correct columns
    Worksheets("setpath").Cells(i, 2).Value = FilesTesters(i - 8)
    Worksheets("setpath").Cells(i, 7).Value = FilesTests(i - 8)
    If FilesTesters(i - 8) = "" And FilesTests(i - 8) = "" Then
        Exit Sub
    End If
Next i
End Sub

 

[Domenic]

The following code uses the FileDialog object to prompt the user to select one or more csv files. If the user cancels, it exits the sub. Otherwise, the collection of selected files is assigned to an object variable...

    Dim folderPath As String
    folderPath = Worksheets("setpath").Cells(5, 3).Value
    
    'make sure path ends in backslash (\)
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If

    Dim selectedFiles As FileDialogSelectedItems
    
    'prompt user to select one or more csv files
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .ButtonName = "Select"
        .Filters.Clear
        .Filters.Add "CSV (Comma delimited) (*.csv)", "*.csv"
        .InitialFileName = folderPath
        .InitialView = msoFileDialogViewDetails
        .Title = "Select Files"
        If .Show <> -1 Then Exit Sub 'user cancelled
        Set selectedFiles = .SelectedItems
    End With

Then, you can simply loop through each file in the collection as follows...

    Dim i As Long
    
    'loop through collection of files
    With selectedFiles
        For i = 1 To .Count
            Debug.Print .Item(i)
        Next i
    End With

Hope this helps!

 

[vlvanriper]

The following code uses the FileDialog object to prompt the user to select one or more csv files. If the user cancels, it exits the sub. Otherwise, the collection of selected files is assigned to an object variable...

    Dim folderPath As String
    folderPath = Worksheets("setpath").Cells(5, 3).Value
   
    'make sure path ends in backslash (\)
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If

    Dim selectedFiles As FileDialogSelectedItems
   
    'prompt user to select one or more csv files
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .ButtonName = "Select"
        .Filters.Clear
        .Filters.Add "CSV (Comma delimited) (*.csv)", "*.csv"
        .InitialFileName = folderPath
        .InitialView = msoFileDialogViewDetails
        .Title = "Select Files"
        If .Show <> -1 Then Exit Sub 'user cancelled
        Set selectedFiles = .SelectedItems
    End With

Then, you can simply loop through each file in the collection as follows...

    Dim i As Long
   
    'loop through collection of files
    With selectedFiles
        For i = 1 To .Count
            Debug.Print .Item(i)
        Next i
    End With

Hope this helps!

I am so sorry to ask you this. Do I change out all of the code or just a portion? Thank you for your assistance.

 

[vlvanriper]

I am so sorry to ask you this. Do I change out all of the code or just a portion? Thank you for your assistance.

I replaced the portion of the code but when I select the .csv files needed nothing happens.

 

[Domenic]

Can you post your amended code?