pull links for all files in folder AND subfolder

I see no difference in output from the original code I submitted. to clarify I got the code to loop through the first level of subfolders, but the loop will not go deeper than 1 level.

Sub Hyperlinkpuller()
Dim objFSO As Object, objFolder As Object, objSubfolder As Object, objFile As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("c:\users\")
i = 1
'loops through each file in the directory
For Each objSubfolder In objFolder.subfolders

For Each objFile In objSubfolder.Files
    'select cell
    Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
    'create hyperlink in selected cell
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        objFile.Path, _
        TextToDisplay:=objFile.Path
    i = i + 1
Next objFile
Next objSubfolder
End Sub

Select allOpen in new window

there must be something that will make it loop through ALL subfolders in the file tree, not just 1 level down.

Instead of using FSO for folder and file iteration, you could shell out a command that does a Dir c:\users /a-d /b /s > c:\temp\dirlist.txt of the parent folder, directing the output to another file.  Then you can read the contents of the dirlist.txt file and populate the worksheet hyperlinks.

is that using excel vba? im confused. I have no access to shell or cmd.

set oWSH = createobject("wscript.shell")
oWSH.Run "cmd.exe Dir c:\users /a-d /b /s > c:\temp\dirlist.txt", 0, True

Select allOpen in new window

yeah that will not work for me, i have zero access to the shell and cmd at my location,

What happens when you execute those two statements?

Note: the cmd.exe needs a /C switch.  This should go before the Dir command

Thanks Bill, it looks like that code is working, the only thing it is not doing is moving beyond cell A293, and if there are more cells, it is overwriting what is existing. is there any way it would insert the links of all files and not just overwrite what is there unless you run the hyperlinkpuller sub?

i made some edits to the code, and it works by having the code changed to

Sub Hyperlinkpuller()
    Dim objFSO As Object
    Dim i As Integer

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

    'Get the folder object
    i = 1
    Call ProcessFolder(objFSO.GetFolder("L:\Global Expert\Buddy"))

End Sub

Sub ProcessFolder(objFolder As Object)
    Dim objFile As Object
    Dim objSubFolder As Object
    
    'loops through each file in the directory
    For Each objFile In objFolder.Files
    
        'select cell
        Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    
        'create hyperlink in selected cell
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=objFile.Path, TextToDisplay:=objFile.Path
        i = i + 1
    Next objFile

    For Each objSubFolder In objFolder.SubFolders
        Call ProcessFolder(objSubFolder)
    Next objSubFolder

End Sub

Select allOpen in new window

thanks for all the help!

You can remove the i  variable using that approach, and all it's usages.

~bp

Note: Integer variables have a 32K max value.