Avatar of david francisco
david francisco
Flag for United States of America asked on

pull links for all files in folder AND subfolder

so I have code that is pulling the links for all files in a folder but I need it to pull for all sub folders as well. the output is going into column a and I would like for it to overwrite anything that is there already. the code I have so far is:

Sub Hyperlinkpuller()
Dim objFSO As Object
Dim objFolder As Object
Dim 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 objFile In objFolder.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
End Sub

Open in new window

Microsoft OfficeVBAMicrosoft ExcelVB Script

Avatar of undefined
Last Comment
aikimark

8/22/2022 - Mon
SOLUTION
Shums Faruk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
david francisco

ASKER
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

Open in new window


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

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.
david francisco

ASKER
is that using excel vba? im confused. I have no access to shell or cmd.
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Shums Faruk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
aikimark

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

Open in new window

david francisco

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

What happens when you execute those two statements?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
aikimark

Note: the cmd.exe needs a /C switch.  This should go before the Dir command
ASKER CERTIFIED SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
david francisco

ASKER
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?
SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
david francisco

ASKER
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

Open in new window


thanks for all the help!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Bill Prew

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

~bp
aikimark

Note: Integer variables have a 32K max value.