http://www.ozgrid.com/forum/showthread.php?t=175318
I came across this VBA code on this site on how to create hyperlinks to a particular folder. Is there a way to modify so that there is no top folder and it starts listing files in column A?
Thanks in advance.
Re: Create hyperlinks to all files in a folder-how to modify code found on site
What do you mean by "No top folder"...?
The listing has to start from somewhere.
It does list the files into Column A.
Re: Create hyperlinks to all files in a folder-how to modify code found on site
For example, if I use c:\folder\folder1\folder2 as the path, in column A1 it will start with c:\folder, column B1 will have c:\folder\folder 1 and column 2 c:\folder\folder1\folder2. I always end up deleting column A1/the top folder because there are no links.
I was looking for VBA code to give me the hyperlinks of the folder path but to start with c:\folder\folder1 in A1 and so on.
Quote from cytop;778272What do you mean by "No top folder"...?
The listing has to start from somewhere.
It does list the files into Column A.
Re: Create hyperlinks to all files in a folder-how to modify code found on site
QuoteI always end up deleting column A1/the top folder because...
Then why not just add a single line to the existing code to delete column A when it finishes?
Re: Create hyperlinks to all files in a folder-how to modify code found on site
I've tried a few times to delete column A and it's not working. What do I need to add to current code to remove column A after it finishes adding all the hyperlinks?
Quote from cytop;778299Then why not just add a single line to the existing code to delete column A when it finishes?
Re: Create hyperlinks to all files in a folder-how to modify code found on site
QuoteI've tried a few times to delete column A and it's not working
Deleting a column is simple - Post one or 2 examples of what you've tried.
Re: Create hyperlinks to all files in a folder-how to modify code found on site
Quote from falcios;778313I've tried a few times to delete column A and it's not working. What do I need to add to current code to remove column A after it finishes adding all the hyperlinks?
Simply delete column A whilst the macro recorder is running and add the generated code to the end of the main subroutine at http://www.ozgrid.com/forum/sh…75318&p=729296#post729296.
However, rather than a kludge to delete the unwanted column(s), I have modified the existing code to start the listing at a specified folder level below the specified start folder. This new functionality is optional and the updated code below largely works the same as the previous code, if the optional startFolderLevel parameter is omitted. The only bug fix in the code below is to prepend an apostrophe " ' " if the folder or file name starts with "@" (without the apostrophe the cell value would be blank).
I wanted to post this updated code in the original thread, since the new functionalilty is optional and it has the "@" fix, however that thread is locked.
Public Sub Hierarchical_Folders_and_Files_Listing3()
Dim startFolderPath As String
Dim startCell As Range
Dim n As Long
startFolderPath = "C:\path\to\folder"
With ThisWorkbook.Worksheets(1)
.Cells.Clear
.Activate
Set startCell = .Range("A1")
End With
'3rd arg = 1 means start output at 1 folder level below specified start folder
n = List_Folders_and_Files3(startFolderPath, startCell, 1)
End Sub
'Optional startFolderLevel argument specifies the folder level at which the listing will start:
'0 (the default) means the listing starts at the start folder.
'1 means the listing starts at 1 subfolder below the start folder
Private Function List_Folders_and_Files3(folderPath As String, destCell As Range, Optional startFolderLevel As Long) As Long
Static FSO As Object
Dim thisFolder As Object, subfolder As Object
Dim fileItem As Object
Dim n As Long
Dim colOffset As Long
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set thisFolder = FSO.GetFolder(folderPath)
'If start folder level has been reached then add a hyperlink for this folder
If startFolderLevel <= 0 Then
colOffset = 1
'Add hyperlink for this folder
destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, _
TextToDisplay:=IIf(Left(thisFolder.Name, 1) = "@", "'", "") & thisFolder.Name
'destCell.Value = IIf(Left(thisFolder.Name, 1) = "@", "'", "") & thisFolder.Name 'folder name without hyperlink
n = 1
Else
colOffset = 0
End If
'List subfolders and files in this folder
For Each subfolder In thisFolder.SubFolders
n = n + List_Folders_and_Files3(subfolder.Path, destCell.Offset(n, colOffset), startFolderLevel - 1)
Next
'If start folder level has been reached then add a hyperlink for each file in this folder
If startFolderLevel <= 0 Then
For Each fileItem In thisFolder.Files
destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, _
TextToDisplay:=IIf(Left(fileItem.Name, 1) = "@", "'", "") & fileItem.Name
'destCell.Offset(n, 1).Value = IIf(Left(fileItem.Name, 1) = "@", "'", "") & FileItem.Name 'file name without hyperlink
n = n + 1
Next
End If
List_Folders_and_Files3 = n
End FunctionDon’t have an account yet? Register yourself now and be a part of our community!