Excel VBA Code to Get List of files

Excel VBA Code to Get List of files

April 27, 2017

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
 

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com