Getting list of files in a folder and listing them in a worksheet

[psycoperl]

How can I get a list of files that are in a particular folder and have them listed as rows in a worksheet?

Here is what I envision the process to be:
1) User selects folder (question on this is posted to How to prompt for a folder path and store in a cell \)
2) A list of files (i.e. PDFs) that are in the folder would be entered into a column on a sheet
3) Apply formulae to modify file names as needed.

I am familiar with VBA in the MS Access Context, not in Excel. So I look forward to your guidance

 

[alansidman]

You can modify this to what you wish to have displayed. This will display file name, last update and file size. You will need someone else to help to modify the file names.

Sub ListAllFile()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add

    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"

    'Loop through the Files collection
    For Each objFile In objFolder.Files
    'If objFile.Name Like "*.pdf" Then
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    'End If
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

 

[offthelip]

try this which allows you to select the folder using the standard filedialog selection. Run the sub getlist
It puts the results into a worksheet called "List"

Public inarr()
Sub Getlist()
Dim cnt As Long
cnt = 1
 Worksheets("List").Select
    If ActiveSheet.AutoFilterMode Then
        If ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
    ElseIf ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
 Range(Cells(1, 1), Cells(20000, 1)) = ""
 inarr = Range(Cells(1, 1), Cells(20000, 1))
 Dim spath As String
 Dim pathn As String

pathn = ActiveWorkbook.Path
FolderName = GetUsrFolder(pathn)

spath = pathn
spath = FolderName
Call Listfiles(spath, cnt)
 Range(Cells(1, 1), Cells(20000, 1)) = inarr
 
 
End Sub
Function GetUsrFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetUsrFolder = sItem
Set fldr = Nothing
End Function

Sub Listfiles(spath As String, cnt As Long)

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim myFolder As Object
    Set myFolder = fso.GetFolder(spath)
    Dim myFile As Object
  
     For Each myFile In myFolder.Files
                   fname = myFile.Name
                 inarr(cnt, 1) = fname
                 cnt = cnt + 1
           
      Next

        '********************************************************************************************
End Sub

 

[Kerryx]

If you doing it yourself Power query
Data -> Get Data -> From File -> Folder
select Folder
This will give you list including File paths which you can Load to Excel sheet, quick and easy