Excel file directory grabber

Question

Currently I have a workbook designed to index a folder, where you enter in a folder path e.g. 'Z:\Example' and it exports all the file names and file paths for everything in that particular folder, into another sheet within the workbook. I was wondering if it would be possible to grab all the files within that folder ('Z:\Example') and if there was any other folders inside that directory, also grab all the files within that folder too.

E.g. I enter 'Z:\Example' into cell A19 (as per the code below), 'Z:\Example' has another folder in it, Z:\Example\Another'. All files within both 'Z:\Example' and Z:\Example\Another' get brought into excel sheet 2.

Private Sub CommandButton1_Click()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer
    Dim Source_Workbook As Workbook
    Dim Target_Path As String

    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Path of the Target Folder
    Target_Path = Range("A19").Value

    Set Target_Workbook = Workbooks.Open(Target_Path)
    Set Source_Workbook = ThisWorkbook

    'Get the folder object

    Set objFolder = objFSO.GetFolder(Target_Path)
    i = 1
    'loops through each file in the directory and prints their names and path

    For Each objFile In objFolder.Files
        'print file name
        Source_Workbook.Sheets(2).Cells(i + 1, 1) = objFile.Name
        'print file path
        Source_Workbook.Sheets(2).Cells(i + 1, 2) = objFile.Path
        i = i + 1
    Next objFile

    'Process Completed
    msgBox "Task Completed"
End Sub

I would prefer to not have to insert all paths that I want indexed at the beginning but if that is unavoidable it is okay. Any help appreciated.

Thanks

Opening the files ( `Set Target_Workbook = Workbooks.Open(Target_Path)` ) is useless. — A.S.H, Feb 03 '17 at 06:56
Sorry, shortened this script from another, must have left it in. Thanks! — user7433828, Feb 03 '17 at 07:00
I guess you can find here to this very same question answers adopting _recursive_ techniques — user3598756, Feb 03 '17 at 07:06
See http://stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba/9832978#9832978 — brettdj, Feb 03 '17 at 07:08

A.S.H · Accepted Answer · 2017-02-03T07:38:24.393

As In the comments, there are many resources to list a folder and its subfolders. This snippet is customized to your application. It uses recursion and needs to be fed the root folder and the target cell where to paste the results.

Private Sub CommandButton1_Click()
    'Call the recursive function
    ListAllFiles ThisWorkbook.Sheets(1).Range("A19").Value, ThisWorkbook.Sheets(2).Cells(2, 1)
    msgBox "Task Completed"
End Sub


Private Sub ListAllFiles(root As String, targetCell As Range)
    Dim objFSO As Object, objFolder As Object, objSubfolder As Object, objFile As Object
    Dim i As Integer, Target_Path As String

    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Get the folder object
    Set objFolder = objFSO.GetFolder(root)
    'loops through each file in the directory and prints their names and path

    For Each objFile In objFolder.Files
        'print file name
        targetCell.Value = objFile.Name
        'print file path
        targetCell.Offset(, 1).Value = objFile.Path
        Set targetCell = targetCell.Offset(1)
    Next objFile

    ' Recursively call the function for subfolders
    For Each objSubfolder In objFolder.SubFolders
        ListAllFiles objSubfolder.Path, targetCell
    Next objSubfolder
End Sub

Excel file directory grabber

1 Answers1