To be able to open all files in a directory using Excel VBA, we can use the Application.Filesearch function to loop on all of the files.

Below, I will show you how to iterate over all files with a particular file extension in a directory, and do a sample action to each file.  In this case, we will change the value of the first cell in the workbook, save and close the file.

I've also written another article on how to use Excel VBA to open a workbook, edit a workbook, save a workbook, and close a workbook.

Sub fileloop()
    Dim MyDir As String
    Dim strPath As String
    Dim vaFileName As Variant
    Dim i As Integer
    
    MyDir = ActiveWorkbook.Path ' current path
    strPath = MyDir & "\files" ' files subdir

    With Application.FileSearch
        .NewSearch
        .LookIn = strPath
        .SearchSubFolders = False
        .Filename = ".xls"

        If .Execute > 0 Then

            For Each vaFileName In .FoundFiles
                ' open the workbook
                Workbooks.Open vaFileName
        
                ' put "Hello" in A1 in each file
                With ActiveWorkbook
                    .Worksheets("Sheet1").Cells(1, 1).Value = "Hello"
                    .Save
                    .Close
                End With
            Next
        End If
    End With
End Sub