Excel VBA Code to Get List of files

This macro works perfectly to get a list of the PDFs (or any other file types) in a directory that you specify in a particular cell on an Excel worksheet.

So on the line beginning, Set objFolder = , you enter the range containing your file path. The file extensions of the files you want a list of are specified four lines down.

So in this example with a file path specified in B1 we get a list like this one, which is also helpful because it puts the file names in one column, and the file paths in another:

Sub GetList() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Dim i As Integer Dim temp As String 'Create an instance of the FileSystemObject Set objFSO = CreateObject("Scripting.FileSystemObject") 'Get the folder object Set objFolder = objFSO.GetFolder("" & Range("G1").Value & "") i = 1 'loops through each file in the directory and prints their names and path For Each objFile In objFolder.Files temp = objFSO.GetExtensionName(objFile.Name) If (temp = "pdf") + (temp = "txt") Then 'print file name Cells(i + 1, 7) = objFile.Name 'print file path Cells(i + 1, 8) = objFile.Path i = i + 1 End If Next objFile End Sub

See the original post here: http://www.ozgrid.com/forum/showthread.php?t=194068