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 ObjectDim objFolder As ObjectDim objFile As ObjectDim i As Integer'Create an instance of the FileSystemObjectSet objFSO = CreateObject("Scripting.FileSystemObject")'Get the folder objectSet objFolder = objFSO.GetFolder("c:\users\")i = 1'loops through each file in the directoryFor 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 + 1Next objFileEnd Sub
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 ObjectDim i As Integer'Create an instance of the FileSystemObjectSet objFSO = CreateObject("Scripting.FileSystemObject")'Get the folder objectSet objFolder = objFSO.GetFolder("c:\users\")i = 1'loops through each file in the directoryFor Each objSubfolder In objFolder.subfoldersFor 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 + 1Next objFileNext objSubfolderEnd Sub
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.
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 SubSub 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 objSubFolderEnd 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.