File and Folder Index with Hyperlinks

  • What I am trying to do is to create an Index of all the files and folders I have in a project folder.
    I want this to be structured in a way that resembles the actual folder structure. Something like this.
    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Master Floder

    [/td]


    [td]

    Sub Folder 1

    [/td]


    [td]

    Sub Sub Folder 1

    [/td]


    [td]

    File 1

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    File 2

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    File 3

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    Sub Sub Folder 2

    [/td]


    [td]

    File 1

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    File 2

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    File 1

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    File 2

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Sub Folder 2

    [/td]


    [td]

    File 1

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    File 2

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Sub Folder 3

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    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 code that can list all files and all folders and sub folders.
    I also want to be able to hyperlink all of them.
    I Might be asking for a bit much but maybe one of you VBA gurus might be able to give me a hand.

    Cheers
    Stephen

  • Re: File and Folder Index with Hyperlinks


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

  • 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


    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.


  • 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:



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


    Cheers.

  • 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:

    Code
    1. startFolderPath = "C:\folder1\folder2"

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

    Code
    1. 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


    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.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --