vlvanriper

New Member
Joined
Nov 10, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,466
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...

VBA Code:
    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...

VBA Code:
    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

New Member
Joined
Nov 10, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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...

VBA Code:
    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...

VBA Code:
    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

New Member
Joined
Nov 10, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,829
Messages
5,627,143
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top