Loop through all folders and subfolders to get file paths

Question

I have a summary sheet which contains sample names in column 1 and another column that contains a hyperlink to a website where the user can download a zip folder that contains raw (run) files of the data. My task is to be able to locate the run file with the same name as in column 1 of the summary sheet e.g. if Sample name is A_02-RD14-0003_23d, my code has to loop through all folders and subfolders in the downloaded zip folder and and locate this sample. With my current code, whenever the sample name in the sheet matches the raw file in the downloaded zip folder, the file path is pasted in column 2. The problem is that the code is very slow. It takes hours. Also, can I make the file path a hyperlink? And can I make the target folder(objFolder) dynamic? As in instead of having this folder path specific to my PC; make it dynamic to allow the user to choose whatever folder path the downloaded zip folder would have been saved. Here is my code...Thank you in advance.

Option Explicit
'run this macro

    Sub ListAllFiles()

        Dim objFSO As Scripting.FileSystemObject
        Dim objFolder As Scripting.folder

            Set objFSO = CreateObject("Scripting.FileSystemObject")
            Set objFolder = objFSO.GetFolder("C:\Users\Magwaveni\Desktop\Run Files\")

            Call GetFileDetails(objFolder)

    End Sub

    Function GetFileDetails(objFolder As Scripting.folder)

    Dim objFile As Scripting.File
    Dim objSubFolder As Scripting.folder
    Dim i, nextrow, lastrow As Long
    Dim sh As Worksheet
    
    Set sh = ActiveSheet
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    'find last row in col 1
    lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row
    'find next blank row in col 2
    nextrow = sh.Cells(Rows.Count, 2).End(xlUp).Row + 1
   
        For Each objFile In objFolder.Files
                For i = 2 To lastrow
                    If Cells(i, 1) = objFile.Name Then
                    Cells(i, 2) = objFile.Path
                    End If
                Next i
            nextrow = nextrow + 1
    
        Next
    
    'Looping throughsubfolders
    For Each objSubFolder In objFolder.subfolders
        Call GetFileDetails(objSubFolder)
    Next
    
    
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
  End Function

Does this answer your question? [Get list of sub-directories in VBA](https://stackoverflow.com/questions/9827715/get-list-of-sub-directories-in-vba) — braX, Aug 20 '20 at 09:29
Not precisely. I only need the file path when the sample name matches that in column 1 — Magwaveni, Aug 20 '20 at 15:09
Well you can just modify the code to handle that condition then, right? — braX, Aug 20 '20 at 17:06

Loop through all folders and subfolders to get file paths

0 Answers0