Looping Through All Files in a Directory

Here are some useful procedures that make extensive use of loops.The first procedure uses VBA's FileSearch object to find all JPG picture files in a certain directory. Each file is listed down a column in Excel.

The outer For i loop iterates through each image file found in a particular folder and all its subfolders. Each pass through the outer loop will return a new path and file name in the variable ThisEntry.To separate the path from the file name,the inner For j loop will search the path and file name from the end to the beginning, looking for the final path separator:

Sub ListJpgFiles()

1 This macro lists all .jpg files 1 in a certain directory to a column in Excel

1 Clear out all cells in the sheet Cells.Clear

' Add Headings

Range("A1:D1").Value = Array("FileName", "Path", "FileName", "NewPath") NextRow = 2

1 Use the FileSearch Object With Application.FileSearch .NewSearch

.LookIn = "C:\MyPictures\" .SearchSubFolders = True .Filename = "*.jpg" .Execute

FilesToProcess = .FoundFiles.Count ' loop through each workbook in the directory For i = 1 To .FoundFiles.Count ThisEntry = .FoundFiles(i) Cells(NextRow, 1).Value = ThisEntry 1 Find to path portion. Start looking from the end ' of This Entry for a backslash For j = Len(ThisEntry) To 1 Step -1

If Mid(ThisEntry, Cells(NextRow, Cells(NextRow, Exit For End If Next j

NextRow = NextRow + 1 Next i j, 1) = Application.PathSeparator Then

0 0

Post a comment