Excel VBA open all files in a directory
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