This VBA code will loop though all the text files in a directory that user picks from an application box and print a list of them. This code can be modified to be used in many scenarios for any file type.
Sub LoopThroughAllTextFilesInFolder() 'Declarations Dim wb As Workbook Dim DirPath As String Dim FileName As String Dim FileExt As String Dim UserFolderChoice As FileDialog Dim i As Integer Dim howmany As Integer 'Declarations End 'Regular Stuff Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'Regular Stuff End 'User's Folder Choice Set UserFolderChoice = Application.FileDialog(msoFileDialogFolderPicker) With UserFolderChoice .Title = "Select A Target Folder" .AllowMultiSelect = False If .Show <> -1 Then GoTo AllGood DirPath = .SelectedItems(1) & "\" End With 'User's Folder Choice End AllGood: 'Check if path is empty If DirPath = "" Then GoTo JumpToEnd 'File Extension FileExt = "*.txt" 'Let's find the first file & how many total files we have FileName = Dir(DirPath & FileExt) howmany = Len(Dir(DirPath & FileExt)) 'let's loop though them For i = 1 To howmany 'print the name of the file Debug.Print FileName 'move to the next file FileName = Dir Next i 'Done!!! JumpToEnd: 'Regular Stuff Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True 'Regular Stuff End End Sub
Posted by Excel Instructor: