locked
MS Access 2016 - Run-time error '445': Set fs = Application.FileSearch RRS feed

  • Question

  • I am converting an 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 am not 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

    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


    • Edited by VickieVanR Friday, November 6, 2020 4:07 PM
    Thursday, November 5, 2020 10:45 PM

All replies

  • Application.FileSearch feature was removed in Office 2010.  See here
    Friday, November 6, 2020 12:27 PM
  • I did see that but I am not really sure how to fix my code.  I am looking for some assistance with my code.  Thank you for your help.
    Friday, November 6, 2020 4:06 PM