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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,326
Office Version
  1. 365
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!
 

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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,326
Office Version
  1. 365
Platform
  1. Windows
Can you post your amended code?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,016
Messages
5,856,835
Members
431,836
Latest member
jj0911

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