File and Folder Index with Hyperlinks

Re: File and Folder Index with Hyperlinks

Try this. Change the start folder path shown as required.

Option Explicit


Public Sub Hierarchical_Folders_and_Files_Listing()


    Dim startFolderPath As String
    Dim startCell As Range
    
    startFolderPath = "C:\folder1\folder2"
    
    Set startCell = Sheets("Sheet1").Range("A1")
    startCell.Parent.Cells.Clear
    
    List_Folders_and_Files startFolderPath, startCell


End Sub




Private Function List_Folders_and_Files(folderPath As String, destCell As Range) As Long


    Static FSO As Object
    Dim thisFolder As Object, subfolder As Object
    Dim fileItem As Object
    Dim n As Long
    
    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set thisFolder = FSO.GetFolder(folderPath)
    
    'Add hyperlink for this folder
    
    destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, TextToDisplay:=thisFolder.Name


    'List subfolders and files in this folder
    
    For Each subfolder In thisFolder.SubFolders
        Set destCell = destCell.Offset(0, 1)
        Set destCell = destCell.Offset(List_Folders_and_Files(subfolder.Path, destCell), -1)
    Next
    
    'Add hyperlink for each file in this folder
    
    n = 0
    For Each fileItem In thisFolder.Files
        destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, TextToDisplay:=fileItem.Name
        n = n + 1
    Next
    
    List_Folders_and_Files = n


End Function

Re: File and Folder Index with Hyperlinks

Hi guys, I am new here. First, congratulations for making this such a good forum and being helpful.

I think the code above is exactly what I need, but I am on a mac
Anyways, I wanted to make it workable on mac and windows. Ideally, is it possible to have the macro automatically identify where (which folder) the workbook is located, and then start indexing files and subfolders from there?

Cheers!

Re: File and Folder Index with Hyperlinks

Hi John

I want to modify it to list file name, size, date modified and folder path with a separate column for hyperlink nameing "open file" but i failed.

Re: File and Folder Index with Hyperlinks

Do you want the output to be in the same hierarchical structured format shown in the original post (with each folder name part of the folder path in separate columns)? If not, then I would start a new thread showing exactly what the output should look like.

Re: File and Folder Index with Hyperlinks

After revisiting this thread I noticed that there is a bug (feature?) in the code posted above which means that folders without any files in them aren't listed. And any subfolders of such folders also aren't listed. The following code fixes this.

Public Sub Hierarchical_Folders_and_Files_Listing2()


    Dim startFolderPath As String
    Dim startCell As Range
    Dim n As Long
    
startFolderPath = "C:\folder1\folder2" 
    
    With Sheets("Sheet1")
        .Cells.Clear
        .Activate
        Set startCell = .Range("A1")
    End With
    
    n = List_Folders_and_Files2(startFolderPath, startCell)


End Sub




Private Function List_Folders_and_Files2(folderPath As String, destCell As Range) As Long


    Static FSO As Object
    Dim thisFolder As Object, subfolder As Object
    Dim fileItem As Object
    Dim n As Long
    
    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set thisFolder = FSO.GetFolder(folderPath)
    
    'Add hyperlink for this folder
    
    destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, TextToDisplay:=thisFolder.Name


    'List subfolders in this folder
    
    n = 0
    For Each subfolder In thisFolder.SubFolders
        n = n + 1 + List_Folders_and_Files2(subfolder.Path, destCell.Offset(n + 1, 1))
    Next
    
    'Add hyperlink for each file in this folder
    
    For Each fileItem In thisFolder.Files
        n = n + 1
        destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, TextToDisplay:=fileItem.Name
    Next
    
    List_Folders_and_Files2 = n


End Function

Re: File and Folder Index with Hyperlinks

Hi Folks, I am very new to this forum, I have seen a few bits of code that will list the all the files or all the folders. But I have yet to find a folder in name of eg(15-03048/15-03047) that can list all files and all folders and sub folders.
I also want to be able to hyperlink the particular contract number in particular folder.
I need to find out the contract number mentioned above in the common path and want to put hyperlink for each contract which i have like goes upto end of thousand and crore.
So i want to enter a contract no in a cell, the parth and link of that folder should open. Kindly help
I Might be asking for a bit much but maybe one of you VBA gurus might be able to give me a hand.

Re: File and Folder Index with Hyperlinks

Hi John W,

I've been using a slightly modified version of your code for quite a while (thanks, btw), and recently found a small issue. Namely, file addresses longer than 255 characters produce an error and are thus just 'skipped' without leaving any evidence (i.e. the corresponding folder may look empty when it's not).

Windows will just not allow you to create folder paths which are longer than 255 characters, so it only happens when the path is already quite close to the length limit, and the file name pushes it over the edge.

The File System Object iterator Folder.Files.Item just doesn't 'see' this long-address files, so the best I could do was print a warning in case the number of files in the folder (which includes these 'ghost' files) is bigger than the actual number of iterations:

'Add hyperlink for each file in this folder
     
    ...
    Dim nfiles As Integer
    ... 
    
    'Add hyperlink for each file in this folder
     
    nfiles = thisFolder.Files.Count    'actual number of files
    n = 1
    For Each fileItem In thisFolder.Files   'files with very long names will not be included in the iteration
        destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, TextToDisplay:=fileItem.Name
        n = n + 1
    Next
    
    'If any number of files were skipped, print a warning. The code could be modified to make a msgbox pop-up or something.
    If nfiles > n - 1 Then
        destCell.Offset(n, 1).Value = "Hidden files, path too long!!"
        destCell.Offset(n, 1).Font.Color = vbRed
        n = n + 1
    End If
        
    List_Folders_and_Files = n
    ...

I tought I'd leave it here in case someone has stumbled upon the same issue.

Cheers.

Re: File and Folder Index with Hyperlinks

Hi,

Get error to GetShortFileName

where is GetShortFileName?

Ok I see where is Function GetShortPathName but the list of file is not good.

Re: File and Folder Index with Hyperlinks

Hi
If it's not too much trouble, can you please give me instructions on how to use this.
Thank you in advance.
Sincerely,
MG

Re: File and Folder Index with Hyperlinks

Open a new workbook, insert a module and paste in the code (see http://www.contextures.com/xlvba01.html#videoreg).

Next, change this line:

startFolderPath = "C:\folder1\folder2"

to the folder path where you want the files and folder listing to start, for example:

startFolderPath = "C:\Program Files"

Save the workbook as a macro-enabled workbook (.xlsm file).

Finally, you can run the macro, Hierarchical_Folders_and_Files_Listing2, by clicking Run on the VBA editor menu, or from Macros on the Excel Ribbon's View tab, or from the Developer tab in the main Excel window - see https://support.office.com/en-…d1-45f5-90a3-50e645fe3155.

Re: File and Folder Index with Hyperlinks

This VBA code is very useful. How can it be changed to start hyperlinks list in column A and not include top folder? Thanks

Re: File and Folder Index with Hyperlinks

Quote from falcios;778229

This VBA code is very useful. How can it be changed to start hyperlinks list in column A and not include top folder? Thanks

Welcome to Ozgrid !

Please can you start a new topic rather than using an old one ?
You can add a link to the other topic if you want.