VBA – Loop Through All (.txt) Files in a User Specified Directory – Excel Macro
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
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:
http://www.houstoncomputerclasses.com/excel-classes/
