Create Hyperlinked List of Files in Subfolders

Philip Treacy

May 20, 2014

I wrote a macro a few weeks ago which created a list of files with hyperlinks to those files, but didn't traverse subfolders looking for those files.  So it was limited to the folder you selected.

This improved macro will :

  • Ask you what your root (starting) folder is
  • Ask you for the file type to look for, e.g. *.xls*, *.jp*
  • Search through all folders below your root folder looking for files that match your criteria
  • Create a hyperlinked list of those files, so clicking on the link will open the file

To do this the macro uses the Dir function, the FileSystemObject (FSO) and recursion.

Update

The code below lists the hyperlinked files in a 'tree' layout, such that each subdirectory is indented to give a clear visual indication of the folder/directory structure.

I had a request to modify the original code to list all the files in a single column though, so here is that code : Hyperlinked File List in Single Column


Dir Function

The Dir function returns the name of a file or folder/directory that matches a specified pattern or file attribute.

So if I am looking for Excel workbooks I would call Dir like this

MyFilename = Dir("d:\sales\*.xls*", 7)

Where MyFilename is a string variable that holds the name of the first file that Dir finds.

The * in *.xls* indicates 0 or more characters, so *.xls* matches file1.xls and file2.xlsx.  You can also use the ? wildcard to match a single character.

The beauty of Dir is that the first time you call it, it returns the first file it finds, but, the next time you call it, it remembers what type of files you are looking for, and then gives you the next in the list.

So after your initial call of Dir, any subsequent calls are simply like this

MyFilename = Dir

You keep calling it until it returns nothing, which indicates that you've found all the files in that particular folder.

I will use Dir to look for the files in the folders we search through.

FileSystemObject (FSO)

The FileSystemObject provides access to the file system on the computer.  It allows you to do things like open and read the contents of files.  You can delete, move or copy files, and importantly for us, you can use FSO to find subdirectories.

By using FSO we can find the subdirectories in our root directory, then look in each of those directories to see if they have subdirectories.  If they do, then we can open each of those subdirectories, and check if they have subdirectories, ad infinitum.

We do this by using a recursive subroutine.  More about that in a minute.

Create a Reference to Microsoft Scripting Runtime Library

In order to access the FileSystemObject we first need to do what's called 'creating a reference' to a library.

Stop, don't run away, it's not that hard really.

All you need to do is open the VBA editor by pressing ALT+F11 in Excel.

Then from the Tools menu, click on References.

Tools, References in VBA Editor

Scroll down the list until you see Microsoft Scripting Runtime, then click the box beside this so it has a tick (check mark).

Microsoft Scripting Runtime

Click OK and you are done.

Well done, you survived 🙂

Recursion

When I was learning to program my lecturer explained recursion this way; if you looked up recursion in a dictionary the definition would read "see recursion".

(I've been waiting 20+ years to use that 🙂 )

Simply put, recursion is when a routine (a VBA sub or a function) calls itself.

What we are doing is looking in folders for subfolders.  If we find a subfolder then we look in it for subfolders.  If we write a sub called ListFilesInSubFolders the algorithm looks something like this.


Sub ListFilesInSubFolders (CurrentFolder)

    List the files in CurrentFolder

    Check CurrentFolder for subfolders and make a list of them

    For every subfolder we find, call ListFilesInSubFolders (SubFolder)

End Sub
 

When ListFilesInSubFolders is called we pass in a value in the brackets ().  Initially we pass in our root/starting folder, and every time we find a subfolder we pass in that subfolder.

What we get is a sub that can work its way through each and every subfolder under the root folder.

Let's say we have a folder structure like this :

Folder Structure

Our root (top) folder is D:\Sales.  This contains a folder called Excel, and that folder contains two more folders, Jan and Feb.

Mind Bending Bit

You don't need to read this to use this macro but it will certainly help you to understand recursion when you come across situations that it would be useful in.  The concept of recursion is straightforward, what I find harder is visualizing the code for a recursive routine.  I sometimes think I can feel my neurons tearing themselves apart and forming new pathways when I try to get my head around some problems.

Let's say we are running a macro SubA.  In our SubA code we then call SubB.  VBA remembers where it was in the code in SubA when it called SubB.  When SubB is finished executing, VBA returns to SubA from where it left it, and continues to run the code in SubA.

It's exactly the same process for recursion, except in recursion SubA is calling SubA.  What VBA does in this case is it essentially makes a copy of SubA and runs that copy.  Where it was up to in the code before calling the copy of SubA is preserved.  So when the copy of SubA finishes, VBA starts executing the original SubA again.

A Real Example

Imagine that each green box in the diagram below represents a copy of the ListFilesInSubFolders  sub.  Each of these copies is created by the code (green box) running in the folder above it.

Once a  green box (the code) has finished executing, VBA returns to the code above it and continues from where it left off.

This is what happens when we call our recursive sub ListFilesInSubFolders

Recursive process

  1. We start the macro and the first folder we pass into our recursive sub ListFilesInSubFolders is our starting folder which is D:\Sales. We list the files in it.
  2. Next we check if D:\Sales has any subfolders, yes it does so we call ListFilesInSubFolders and pass it the folder D:\Sales\Excel
  3. List the files in here.  Then we check if D:\Sales\Excel has any subfolders.  Yes it does, so we call ListFilesInSubFolders  and pass it D:\Sales\Excel\Jan
  4. List the files here.  Does D:\Sales\Excel\Jan have any subfolders?  No, it doesn't, so this code is finished.  This lets VBA go back to where it left off with D:\Sales\Excel in Step 3
  5. Does D:\Sales\Excel have any more subfolders, yes.  We call ListFilesInSubFolders   and pass it D:\Sales\Excel\Feb
  6. List files in here.  Does D:\Sales\Excel\Feb have any subfolders?  No, so this code is finished.
  7. D:\Sales\Excel doesn't have any more subfolders.  Stop running this copy of ListFilesInSubFolders  and return to the code running in D:\Sales
  8. D:\Sales doesn't have any more subfolders, so the macro finishes.

Be Careful

Because this macro will search through all directories below the starting folder, if you have a lot of folders and a big hard drive, the routine could take a long time.

How To Use This Macro

In the workbook I've provided, I've created a shape on Sheet1 and assigned the macro to that.  So just click the shape to run it.

You can copy/paste the macro into your Personal.xlsb, or create a shortcut/shape of your own.

Before you run the macro you need to click into a cell on the active worksheet.  This is where the list of files will start being created from.

If you want to use the same cell every time then find the line of code that specifies the Starting Cell and change that to your preferred cell, e.g. "A1" to always start the list from A1.

Choose the starting cell

When the macro starts it asks you to choose your starting folder.

Choose the starting folder

It then asks you what files to search for.  You can use the standard * and ? wildcards here, or click OK to list all files.

Specify files to search for

The Hyperlinked File List

You will now have a nice list of hyperlinks to all the files you want, indented at each subdirectory to make it easier to read.

Hyperlinked file list

The Code

As usual you can get the code in an Excel workbook.

Enter your email address below to download it.

By submitting your email address you agree that we can email you our Excel newsletter.

Interactive Excel Web App Dashboard

Writing Excel Formulas Efficiently

143 thoughts on “Create Hyperlinked List of Files in Subfolders”

  3. Hi,
    Thanks for the great code!
    I understand that using DIR is faster in processing, however, it can not process deep long path, as the code always come back with error (file not found). Can the code be modified to produce the same output for directories with long path?

    Thanks, Zak

    Reply

    • Hi Zak,

      So you have file paths greater than 260 chars? You have enabled this in W10?

      https://learn.microsoft.com/en-us/windows/win32/fileio/maximum-file-path-limitation?tabs=registry

      Microsoft’s documentation for Dir makes not mention of a character length limit

      https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function

      So I’m not sure that it would error if it has to work with a path > 260 chars.

      You’ve confirmed that the folders you are trying to reach actually exist?

      regards

      Phil

      Reply

      • I need to mention that I read on another forum that I cn use \\?\ before the network path, that should work. However, cannot incorporate in your original code.

        Reply

        • Hi Zak,

          Find this line of code and add \\?\ as shown here

          TargetFiles = “\\?\” & StartingFolder.Path & “\” & FileType

          regards

          Phil

          Reply

      • Hi Phil,
        Many thanks for the prompt reply.

        Yes, I have file paths greater than 260 chars. I am not allowed to enable this in W10 as it involves setting the Registry, which is not permitted on the organization’s network.

        I confirmed that the folders I am trying to reach actually exist. The code runs perfectly on all subfolders until it reaches a long path, then it comes with error (file not found) on “CurrentFilename = Dir(TargetFiles, 7)”.

        I also have another request to add additional column showing the total number of files per each folder as well as the total size, including the starting folder.

        Thank you for your patience.

        Reply

        • Hi Zak,

          Can you please post this additional question on our forum with an example of how you want the results to look. Someone there will be able to help.

          Regards

          Phil

          Reply

  4. This is a brilliant little macro that will be saving me a large amount of time, thank you!
    I’ve edited a couple of things including the part to indent the sub folders into a new coloun from in the comments.

    What I’m a little stuck with is trying to get the subfolders to be grouped and then collapsed.
    I’ve been able to group the cell that the sub folders name copies to using “DestinationRange.Rows.Group” at the very end of the procedure. Would anyone have any ideas that I could try to group the sub folder contents rows and collapse them?

    For Each SubFolder In StartingFolder.SubFolders
    ListFilesInSubFolders SubFolder, DestinationRange
    Next SubFolder
    SubFolder
    DestinationRange.Rows.Group

    End Sub

    Reply

  5. Thank you so much! This was exactly what I was looking for. However, I have a question. Is there a way to do the same thing but rather than accessing a local or mapped drive, accessing a Sharepoint drive for the folders/files?

    Reply

  6. Hello!

    I downloaded but unfortunately it only appears in a single column.
    Can you fix the download?
    I want the treeview version please, I would appreaciate it a lot.

    Thanks in advance

    Reply

    • Hi Raul,

      Which file did you download? The one at the bottom of the post under the heading The Code lists the files in a tree format.

      Regards

      Phil

      Reply

      • Sorry for the late reply!

        Finally I was able to fix it by modyfing the DestinationRange as one user said.

        I have an other request (sorry hahaha) I was thinking about making the folders appaear in black instead of blue and in capital letters.
        Unfortunately I’m a noob and I it’s not working properly. The first folder of a folder always appears in blue as a any other file while the others folders appears properly (black)…I send you the code and I would really appreciate your help! Your code is a beautiful work

        ‘Write name of folder to cell
        DestinationRange.Value = StartingFolder.Name
        DestinationRange.Hyperlinks.Add Anchor:=DestinationRange, Address:=StartingFolder.path, TextToDisplay:=UCase(StartingFolder.Name)

        With Selection.Font
        ‘.Name = “Calibri”
        .Bold = False
        ‘.Size = 9
        .Underline = xlUnderlineStyleSingle
        .ThemeColor = xlThemeColorLight1

        End With

        ‘Get the first file, look for Normal, Read Only, System and Hidden files
        TargetFiles = StartingFolder.path & “\” & FileType

        CurrentFilename = Dir(TargetFiles, 7)

        OffsetRow = 1

        Do While CurrentFilename “”

        ‘Create the hyperlink
        DestinationRange.Offset(OffsetRow).Hyperlinks.Add Anchor:=DestinationRange.Offset(OffsetRow), Address:=StartingFolder.path & “\” & CurrentFilename, TextToDisplay:=(CurrentFilename)

        OffsetRow = OffsetRow + 1

        ‘Get the next file
        CurrentFilename = Dir

        Loop

        Reply

        • Hi Raul,

          Delete this 

          With Selection.Font
‘.Name = “Calibri”
.Bold = False
‘.Size = 9
.Underline = xlUnderlineStyleSingle
.ThemeColor = xlThemeColorLight1
End With

          and add 1 line here as shown in red

          'Write name of folder to cell
    DestinationRange.Value = StartingFolder.Path
    DestinationRange.Hyperlinks.Add Anchor:=DestinationRange, Address:=StartingFolder.Path, TextToDisplay:=UCase(StartingFolder.Name)
    DestinationRange.Font.Color = RGB(0, 0, 0)

          regards

          Phil

          Reply

  7. Excellent tool. I was wondering is it possible to create hyperlinks for the folders and subfolders the same as they create for the file.

    Reply

    • Thanks Darshan.

      You can create a link to the folder(s) by adding 1 line of code. In the function ListFilesInSubFolders find this section :

      'Write name of folder to cell
    DestinationRange.Value = StartingFolder.Path

      and add this after 

          DestinationRange.Hyperlinks.Add Anchor:=DestinationRange, Address:=StartingFolder.Path, TextToDisplay:=StartingFolder.Path

      regards

      Phil

      Reply

  8. Hi,

    When running the tabulated workbook version I keep getting errors and the code stopping at the line:

    NewRow.Cells(1, 2).Hyperlinks.Add Anchor:=NewRow.Cells(1, 2), Address:=StartingFolder.Path & “\” & CurrentFilename, TextToDisplay:=CurrentFilename

    but cannot see what is causing this. Are there any file extensions which could cause this issue.

    Thanks David.

    Reply

    • Hi David,

      Without seeing your code it’s hard to say what the issue is. Perhaps the StartingFolder or CurrentFilename are invalid. What are the values for these when you get ban error? What exactly is the error message you get?

      If you start a topic on our forum and attach your file we can take a look at your code.

      Regards

      Phil

      Reply

      • Hi Phil,

        I have had a bit more of a look at it and it seems to be stopping at row 32767 in the table and does not go any further.

        I have looked at the other folders where the same problem has occurred and they have also stopped in the same position so it looks like an issue with going beyond 32767 rows in excel – any ideas?

        Reply

        • Hi David,
          The row parameter might have been declared of Integer type, the value 32767 is the integer limit.
          Just go to the declarations section and change from Integer to Long.

          Reply

  9. Sadly, on this image

    https://d13ot9o61jdzpp.cloudfront.net/images/recursive-process.jpg

    purple circle (go back to where it left off) process doesn’t work at all.

    Could you please check your upload Excel file again?

    L.E.:
    I did!
    Set DestinationRange = DestinationRange.Offset(OffsetRow)
    → Set DestinationRange = DestinationRange.Offset(OffsetRow + 1, 1)

    ‘Set DestinationRange = DestinationRange.Offset(1)
    ‘DestinationRange.Select
    → Set DestinationRange = DestinationRange.Offset(0, -1)
    → DestinationRange.Select

    Reply

      • Actually the section needs to be like this for me so I don’t get extra rows:

        Set DestinationRange = DestinationRange.Offset(OffsetRow, 1)

        ‘ For each SubFolder in the current StartingFolder call ListFilesInSubFolders (recursive)
        ‘ The sub continues to call itself for each and every folder it finds until it has
        ‘ traversed all folders below the original StartingFolder
        For Each SubFolder In StartingFolder.SubFolders
        ListFilesInSubFolders SubFolder, DestinationRange

        Reply

    • Hi this fix worked for me. It looks like the download under “The Code” hasn’t been fixed yet.

      For anyone on Windows 10 / Excel 365 where the results were a single column and not a hierarchical tree try these changes.

      Reply

  11. Hi Phil, thanks for the macro. I have a question: I would like to add some notes adjacent to the file name, eg in cell C4 for east.docx (cell B4). At the same time, I may add new files in the folder and reapply the macro, which may change the location of the original file (eg east.docx to cell B6). Is there a way to move my note together with the file name (eg to cell C6)? Thanks, Lei

    Reply

    • There is always a way, but not always easy.
      You have to read the list of files and your notes from the sheet before starting again the code that creates the list.
      Load the info in a dictionary with filepath as the key, and when code runs, check the dictionary for notes.
      Try our forum, you will be able to load a sample file and get help.

      Reply

  12. Hi this code is incredibly useful but I’m looking to make a couple of changes to it and I can’t quite get it to do what I’m intending. I’m trying to change it so when I filter it to a specific file name such as “pressure” it will ONLY list the files that are hyperlinked from the filter. Ideally I can put in a file name filter, have it hyperlink the files that fit the criteria, move down 4 cells and then loop until it goes through all folders and subfolders until it finishes. Sorry if this is confusing I’m just a bit lost!

    Reply

    • Hi Austin,

      This code already does that. For example if you search for pgt-* it will list all files beginning with pgt-

      If there’s something else you need it to do differently please start a topic on the forum and attach your file so I can see the code you are writing.

      Regards

      Phil

      Reply

  13. Hi,

    Thank you for the great content and good coding.
    I have tried but to no avail, is it possible to have the folder listed without the path, just the folder name?

    Reply

    • Hi Juan,
      Use StartingFolder.Name instead of StartingFolder.Path
      ‘Write name of folder to cell
      DestinationRange.Value = StartingFolder.Path

      But will not make things clear, might be confusing…

      Reply

      • Hi Catalin,
        You are a star. Thank you I couldn’t to the life of me figure out how to do it. Works Perfectly now thank you once again.

        Reply

  14. Hi Mynda

    I ran the macro but got got an error message & below code line in the 2nd Sub was highlighted in yellow:
    CurrentFilename = Dir(TargetFiles, 7)

    However, thereafter, I found list of files appeared in my excel list.
    I thought I should bring this to your notice.
    Wonderful work. Thanks.

    Reply

    • Hi Sandeep,

      It works fine for me so not sure why you received an error initially but then it works fine.

      Regards

      Phil

      Reply

  16. Thank you for this code… It is a great macro. I would like to get the macro to run on other sheets as well.

    If I assign a new Start location:

    StartingCell = ActiveSheet.Range(“O3”).Select
    or
    StartingCell = (“O3”)
    or
    StartingCell = Range(“O3”)

    The code stops at With Range(StartingCell)

    I can’t alter the With Range(StartingCell) either. And the macro will not run without it.

    Any suggestions, so that I can deploy that fantastic macro within other worksheets of the workbook?

    Thank you.

    Robert

    Reply

    • Hi Robert,
      As written in code, Starting cell is declare as a string, it can be a text only.
      Dim StartingCell As String ‘Cell where hyperlinked list starts

      A string can be what we already have: StartingCell = ActiveCell.Address (the address of a cell: A4, O6, and so on is a string)
      You can assign a string: StartingCell= “O3” , or StartingCell= Range(“O3”).Address
      You cannot use StartingCell= Range(“O3”), because Range(“O3”) is a range object type, not a string.
      There is an indication in code:
      ‘Make this a cell address to insert list at fixed cell
      ‘e.g. StartingCell = “A1”
      Cheers
      Catalin

      Reply

  17. Dear Phil
    Thank you so much for this macro. I have found it very useful. However, my bosses now want to have hyperlinks to the folders. We need to save space on our server so I have to list the contents for decisions to be made about what is not needed. We need to be able to open the folders to see what is in them because that is clearer than the list. Please will you adapt the code for this too.

    By the way, you do not have to autofit the columns because there is only one item per row so Excel displays the whole filename anyway across the empty columns beside it. I like to have the indentations to show where the folders begin and end, but they only need to be four characters wide. I put one colour on the left of the folders and another colour on the left of files so that I can filter by colour to show only folders.

    Is there a way to code for exceptions, so that those irritating files called “thumbs.db” and “desktop.ini” are not included, as well as open files that start with “~”?

    Another thing you could teach us one day if possible is how to expand and collapse the list to show or hide the files. You can only do it for one column at a time with filters. I am putting the different directories/folders into different worksheets manually now because a single listing is too unwieldy with the all the files showing..

    Regards and many thanks
    Anne

    Reply

    • Hi Anne,

      It sounds like you really need another tool for this. If you are preparing a list of all files and folders on th whole server, why not just look through the files using Windows Explorer?

      But you can do what you want in excel – here’s the modified code

      Regards

      Phil

      Reply

  18. Hi,

    Thanks for the VBA code, this is extremely helpful. I am however wondering how I can amend the code so that the file name appears in Column A and the Hyperlink to the file in the adjoining Column B? Further if 2 files exist in the folder/sub folder I would like the folder name for the second file to be repeated so that each hyperlink value in Column B has a corresponding Folder Name in Column A.

    Hope you can assist!

    Kind regards,

    Jon

    Reply

      • Hello, I really like this tabular version. Is it possible to add in one more column to include the last modification time of the file? Thanks

        Reply

        • Sure, add a new column in the table , named “Last Modified”
          Add this line in ListFilesInSubFolders procedure:
          Dim fs as Object: Set fs = CreateObject(“Scripting.FileSystemObject”)

          Add this line that writes the modified date, (after Set NewRow = LinksTable.ListRows(LinksTable.ListRows.Count).Range):
          NewRow.Cells(1, 3).Value = fs.GetFile(StartingFolder.Path & “\” & CurrentFilename).DateLastModified

          Reply

          • thanks I tried these but got a Compile error, saying “Variable not defined” pointing to Scripting in “Dim fs as Object: Set fs = CreateObject(“Scripting.FileSystemObject”)”

          • What operating system are you using? Is it a Mac? (will not work on a Mac…)
            If you are using a windows OS, try replacing the double quotes, might be the wrong ones if pasted from browser.

  20. Thank you for posting this macro. I’ve been successfully using it, and it is a real time saver.

    I would like to know if there is a way to exclude a folder? If so, what code needs to be added/edited?

    Thank you.

    Reply

    • Hi,
      You can create a string with a list of folders to exclude:
      ExcludeFolders=”Folder name 1, Folder name 2, Any other folder name”
      Then change this line:
      ListFilesInSubFolders FSOFolder, ActiveCell
      to:
      If Instr(ExcludeFolders,FSOFolder.Name)=0 Then ListFilesInSubFolders FSOFolder, ActiveCell
      Make sure your excluded folder name is not included into other folder names that you really want to list files from…

      Reply

      • I added these lines to the code, but it isn’t excluding the folders. Did I do something wrong?

        Dim ExcludeFolders As String
        ExcludeFolders = “.TEMPORARY ITEMS, APRILS – do not touch”

        If InStr(ExcludeFolders, FSOFolder) = 0 Then
        ListFilesInSubFolders FSOFolder, ActiveCell
        End If

        Reply

        • I think it’s my fault, FSOFolder is an object, not a string, to be able to compare the items they need to be both strings.
          Use:
          If InStr(ExcludeFolders, FSOFolder.Name) = 0 Then

          Reply

          • I have to say, this code is amazing. I had written a manual way to get this done using powershell and excel and the hyperlink function, but this simply blows that out of the water all together, it is so awesome!!! Thank you!!! However, I do have a question/request: Is there a way to get the code written do be able to disregard selected folders. As an example, I want an index of 10 folders out of the 12 due to content or obsolete data, is there a way for this to happen? I get the option of “this is the folder I want indexed…let me see which folders are here, and then allow me to select the specific folders. Is that an option?

          • Hi Brandon,
            If you want to pick folders from a list to exclude, that means redesigning the code, to display the list in a dialogbox, with checkboxes to include/exclude.
            The easy way is to add in code a list of folder names to exclude:
            in the ListFilesInSubFolders procedure, replace:
            Do While CurrentFilename <> “”
            with:
            Dim ExcludeList As String: ExcludeList = “_Folder Name 1_, _Folder Name 2_”
            Do While CurrentFilename <> “” And InStr(1, ExcludeList, “_” & CurrentFilename & “_”, vbTextCompare) = 0

          • Is it an option or how does one go about getting the code written to handle excluding folders? Also, I am looking for the file that indents the subfolders, but I can not find it. I have the tabular version, but I really would like the indented version that the links reference. Is that available somewhere?
            L.E.: Is there a copy of this that can be downloaded with the extra properties?

          • Hi Brandon,
            Just above the comments section, at the end of the article, there is a button to download workbook. Here is a link.
            Replace the line I indicated with those 2 lines provided for excluding a list.
            If you want to build the more complex version that allows you to select subfolders to exclude, you can try our forum, but a forum will help you adjust the codes, usually a forum is not for providing the complete solution, most of the work is assumed to be yours if you know how to add a form and code. Or you can hire someone to do that, that’s the second option.
            Regards,
            Catalin

          • Hi Catalin,
            Thanks for the information. The link I was looking for had the folder name and file names in two different columns, like the example had shown. but the link here has them in the same column. I’ll make it work though! It is still better than doing it manually!

            Regarding writing the code and making tweaks to it, my request was similar to others in the comments that asked for changes. It would appear from the time this was posted (the original code) till recently, processes have changed a little bit.

            Thank you for the code I need to add in, I’ll put it in and work with it!

            This is still awesome code and information!

          • Hi Brandon,
            Glad to hear you like the code.
            We never write custom solutions as part of our support. What you asked for takes more time to code then any other change request, that’s why I suggested to post the request on our forum, see you there in case you still need help with this change.
            Cheers,
            Catalin

  21. I have downloaded the file that provides the result in one column but wanted to create the output where each subfolder is in another column, as in the example. Tried various ways without success. Could you please send me the macro or file that splits the subfolders into different columns?

    Thanks

    Reply

  22. This tool is very useful (I downloaded the workbook further down that displays Folder Path in one column and File Name in the next). For that workbook, is there a way to make the links relative instead of absolute?

    For example, I have a root folder “USB Stick” with a handful of sub-folders underneath and files other sub-folders/files nested in those. After I create the list, I want to be able to save it from Excel as an HTML page and stick it in the root folder (USB Stick) so that the set of folders/files can be put on a USB stick and the links from the HTML file in the USB Stick root will still work, no matter which “drive letter” the system assigns to the USB stick. Is there a way to do that?

    L.E.:I also tried the code above and that is very nice, too. I have the same question regarding that — how to make the links relative?

    Thanks!

    Reply

  24. I like your tips. But I got an error in strings by Unicode (exclude a Latin characters). i think a dir function not to run when get it. Can you edit for Create-Hyperlinked-List-of-Files-in-Subfolders-Table Version.xlsm? (I love your table very much!). When it get a string by Unicode, it can not extract files or subfolders. Please help me. Thanks very much! I don’t know about VBA.

    Reply

  25. Could you create a version of this where Column A is the folder path and Column B is the filename with both columns hyperlinked?

    If there are several files in a folder, the value in Column A is repeated for each individual file displayed in Column B.

    Reply

      • This file is fabulous – exactly what I was looking for. Having quick access to either the folders or the files.
        Many thanks for your hard work and for publishing.

        Reply

        • Interesting, there is no limitation to Integer in the OneDrive file code.
          In the original version, the only numeric variable is also declared as Long: Dim OffsetRow As Long.
          Where did the code stopped?

          Reply

          • Run-time error ‘1004’:

            Application-defined or object-defined error.

            Clicking debug, it highlighted this line:

            NewRow.Cells(1, 1).Hyperlinks.Add Anchor:=NewRow.Cells(1, 1), Address:=StartingFolder.Path, TextToDisplay:=StartingFolder.Path

            within:

            ‘Get the first file, look for Normal, Read Only, System and Hidden files
            TargetFiles = StartingFolder.Path & “\” & FileType

            CurrentFilename = Dir(TargetFiles, 7)

            Do While CurrentFilename “”
            LinksTable.ListRows.Add
            Set NewRow = LinksTable.ListRows(LinksTable.ListRows.Count).Range
            ‘Write name of folder to cell
            NewRow.Cells(1, 1).Hyperlinks.Add Anchor:=NewRow.Cells(1, 1), Address:=StartingFolder.Path, TextToDisplay:=StartingFolder.Path
            ‘Create the file hyperlink
            NewRow.Cells(1, 2).Hyperlinks.Add Anchor:=NewRow.Cells(1, 2), Address:=StartingFolder.Path & “\” & CurrentFilename, TextToDisplay:=CurrentFilename

            ‘Get the next file
            CurrentFilename = Dir

            Loop

          • Hi Karl,
            Can you please create a new topic in our forum and upload your test file, so I can debug the error?
            Hard to see why it fails.
            Thank you

  26. The macro-enabled file worked best for me. This produced exactly what was taking me all afternoon to create individual hyperlinks. Now my committee will be able to access their files more accurately and speedily.
    Thanks

    Reply

  27. Hi Phil

    The code runs great but I encountered a error when searhing a large directory.

    Run-time error ’52’
    Bad file name or number

    Debug – highlights code: CurrentFilename = Dir(TargetFiles, 7)

    Anyway around this to keep it running and ignore the bad file.

    Cheers

    Reply

    • Hi Dean,
      Dir method fails sometimes, especially on network files due to bad/inaccesible unc.
      FileSystemObject is more likely to work, and it’s already declared as public in the module.
      Instead of a “Do Loop While” loop, use another loop using FSO, and compare the file name with the FileType:

      Sub ListFilesInSubFolders(StartingFolder As Scripting.Folder, DestinationRange As Range)
      ' Written by Philip Treacy, https://www.myonlinetraininghub.com/author/philipt
      ' My Online Training Hub https://www.myonlinetraininghub.com/Create-Hyperlinked-List-of-Files-in-Subfolders
      ' May 2014
      ' Lists all files specified by FileType in all subfolders of the StartingFolder object.
      ' This sub is called recursively

      Dim CurrentFilename As String
      Dim OffsetRow As Long
      Dim TargetFiles As String
      Dim SubFolder As Scripting.Folder
      Dim FileObj As Object
      'Write name of folder to cell
      DestinationRange.Value = StartingFolder.Path

      OffsetRow = 1

      For Each FileObj In StartingFolder.Files
      If FileObj.Name Like FileType Then
      DestinationRange.Offset(OffsetRow).Hyperlinks.Add Anchor:=DestinationRange.Offset(OffsetRow), Address:=StartingFolder.Path & "\" & CurrentFilename, TextToDisplay:=CurrentFilename
      OffsetRow = OffsetRow + 1
      End If
      Next FileObj

      ' Offset the DestinationRange one column to the right and OffsetRows down so that we start listing files
      ' inthe next folder below where we just finished. This results in an indented view of the folder structure
      Set DestinationRange = DestinationRange.Offset(OffsetRow, 1)

      ' For each SubFolder in the current StartingFolder call ListFilesInSubFolders (recursive)
      ' The sub continues to call itself for each and every folder it finds until it has
      ' traversed all folders below the original StartingFolder
      For Each SubFolder In StartingFolder.SubFolders

      ListFilesInSubFolders SubFolder, DestinationRange

      Next SubFolder

      ' Once all files in SubFolder are listed, move the DestinationRange down 1 row and left 1 column.
      ' This gives a clear visual structure to the listing showing that we are done with the current SubFolder
      ' and moving on to the next SubFolder
      Set DestinationRange = DestinationRange.Offset(1, -1)
      DestinationRange.Select

      End Sub

      Cheers,
      Catalin

      Reply

      • Thanks Catalin. Appreciate the help.

        What I managed to do yesterday was put in a error trap for encountered folder/file errors such as ‘access denied’ or ‘bad file name’ which was stopping the macro.

        Basically if it encounters the error such as 52 it clears the error and continues to next available folder/file.

        I’m a novice but I managed to put this in which worked.

        ‘Get the first file, look for Normal, Read Only, System and Hidden files
        TargetFiles = StartingFolder.Path & “\” & FileType

        ‘Added by Dean.
        ‘Error trap for if folder access denied or bad file name or similar.
        On Error Resume Next

        Dim stringvariable As Long

        stringvariable = “”
        stringvariable = Dir(“\\a\long\unc\path”, vbDirectory)

        If Err.Number 0 Then
        ‘effectively invalid unc server, take whatever action you deem appropriate.
        Err.Clear
        End If

        If stringvariable “” Then
        ‘found the directory, so do what you need to do.

        CurrentFilename = Dir(TargetFiles, 7) ‘Whilst executing code it can error here if folder is access denied or bad file name. Added Error Trap code above.

        OffsetRow = 1

        Do While CurrentFilename “”

        ‘Create the hyperlink
        DestinationRange.Offset(OffsetRow).Hyperlinks.Add Anchor:=DestinationRange.Offset(OffsetRow), Address:=StartingFolder.Path & “\” & CurrentFilename, TextToDisplay:=StartingFolder.Path & “\” & CurrentFilename

        OffsetRow = OffsetRow + 1

        ‘Get the next file
        CurrentFilename = Dir

        Loop

        End If

        Reply

  28. Fantastic code. Whilst ‘mind boggling’ I was able to excute really easily. Many thanks.

    I have question though, if I just want all the file hyperlinks listed in one column can you describe the changes to the code to do this?

    Cheers

    Reply

    • Hi Phil

      Much appreciated.for the file.

      I must of had a brain freeze, I actually meant one column, doh!

      Before testing the file you posted for me I did a work around using your original code then stacking the columns in new column and removing empty cells then deleting the redundant columns.

      I really for love to do it easier like you did with the rows but as a column.

      My VBA knowledge is limited.

      Hope you dont mind. Once again big thanks for taking the time to reply.

      Reply

  29. This is the most awesome code for this issue . Great work man & great explanation !
    Although it’s a little bit old post, it’s still the most useful one I have found now !!
    Many thanks 🙂

    Reply

  30. Hi, Phil
    This is awesome! When I run it for small directories it works great. But on a larger one I keep getting a Runtime error ‘S2 and the code is highlighted in yellow at:
    currentFilename=Dir(TargetFile7)
    I’ve ran it a couple of times and it always hangs up at the same place (although it does pump out the first 8316 rows).
    Any ideas, please?

    Reply

    • Hi Brenda,
      The size of the directory is irrelevant. If the file name contains special chars, Dir will raise an error 52. Use a FileSystemObject based code to loop through that directory and list all files names, then you will be able to identify the one which contains special chars.
      Catalin

      Reply

  32. Catalin,

    Thanks for your very kind and quick support. I confirmed the sub of your provision works OK.
    Seems I need to do the further fine tuning to satisfy what I want.

    Kindest regards,

    Yoshi
    Japan

    Reply

  33. Catalin,

    Thanks for your quick note. I know it and that’s very common approach as proposed at other sites. But what I intended to ask is to add those other file properties next to the tree structure of what is provided/proposed here.
    In another word, what I want is the tree-structured file listing with those properties.
    Can you make it?

    Another thanks.

    Yoshi

    Reply

    • Hi Yoshi,
      Use this version to list those properties:
      Sub ListFilesInSubFolders(StartingFolder As Scripting.Folder, DestinationRange As Range)
      ' Written by Philip Treacy, https://www.myonlinetraininghub.com/author/philipt
      ' My Online Training Hub https://www.myonlinetraininghub.com/Create-Hyperlinked-List-of-Files-in-Subfolders
      ' May 2014
      ' Lists all files specified by FileType in all subfolders of the StartingFolder object.
      ' This sub is called recursively

      Dim CurrentFilename As String
      Dim OffsetRow As Long
      Dim TargetFiles As String
      Dim SubFolder As Scripting.Folder

      'Write name of folder to cell
      DestinationRange.Value = StartingFolder.Path

      'Get the first file, look for Normal, Read Only, System and Hidden files
      TargetFiles = StartingFolder.Path & "\" & FileType

      CurrentFilename = Dir(TargetFiles, 7)

      OffsetRow = 1

      Do While CurrentFilename <> ""

      'Create the hyperlink
      DestinationRange.Offset(OffsetRow).Hyperlinks.Add Anchor:=DestinationRange.Offset(OffsetRow), Address:=StartingFolder.Path & "\" & CurrentFilename, TextToDisplay:=CurrentFilename
      DestinationRange.Offset(OffsetRow, 1) = FSO.GetFile(StartingFolder.Path & "\" & CurrentFilename).DateCreated
      DestinationRange.Offset(OffsetRow, 2) = FSO.GetFile(StartingFolder.Path & "\" & CurrentFilename).DateLastModified
      DestinationRange.Offset(OffsetRow, 3) = FSO.GetFile(StartingFolder.Path & "\" & CurrentFilename).Size

      OffsetRow = OffsetRow + 1

      'Get the next file
      CurrentFilename = Dir

      Loop

      ' Offset the DestinationRange one column to the right and OffsetRows down so that we start listing files
      ' inthe next folder below where we just finished. This results in an indented view of the folder structure
      Set DestinationRange = DestinationRange.Offset(OffsetRow, 4)

      ' For each SubFolder in the current StartingFolder call ListFilesInSubFolders (recursive)
      ' The sub continues to call itself for each and every folder it finds until it has
      ' traversed all folders below the original StartingFolder
      For Each SubFolder In StartingFolder.SubFolders

      ListFilesInSubFolders SubFolder, DestinationRange

      Next SubFolder

      ' Once all files in SubFolder are listed, move the DestinationRange down 1 row and left 1 column.
      ' This gives a clear visual structure to the listing showing that we are done with the current SubFolder
      ' and moving on to the next SubFolder
      Set DestinationRange = DestinationRange.Offset(1, -4)
      DestinationRange.Select

      End Sub
      Catalin

      Reply

  34. Very Good One. The logic employed here is very different from others. What I am missing here are the other data such as the modified date, the created date and file size. Any approach to add those into this excellent tool? Another thanks.

    Reply

    • Hi Ohno,
      If you use the File System Object, you can extract many file properties:
      Set FSO = New Scripting.FileSystemObject
      Set SourceFolder = FSO.GetFolder(SourceFolderName)
      i = 2
      For Each FileItem In SourceFolder.Files
      Cells(i, 1) = FileItem.Name
      Cells(i, 2) = FileItem.DateCreated
      Cells(i, 3) = FileItem.DateLastModified
      Cells(i, 4) = FileItem.Size
      i = i + 1
      Next FileItem
      Set FSO = Nothing
      Catalin

      Reply

  36. Hi,
    I’m novice in VBA and the above code is showing compile error on “Public FSO As New FileSystemObject” as user-defined type not identified. So, please help me

    Reply

    • Hi Shivesh,

      Have you created the reference to the Microsoft scripting runtime? See the section above titled “Create a Reference to Microsoft Scripting Runtime Library”

      Regards

      Phil

      Reply

      • Oh! Great,

        Thanks a lot Mr. Phil.

        If you don’t mind then @one more thing on which I would like to take help from you !!

        Exactly, I’m having a list of files name e.g. “a.xlsx, b.xlsx, c.xlsx etc.” and I have lot folders & sub folders from which these files could be copied, but I’m having thousand of files and It’ll take a lot of time to copy one by one.:'( .

        So, is there any way to use a macro in which I’ll describe the list of file and it’ll search from multiple folders & sub folders and copy it to the desired one particular location??

        Thank you thank you thank you so much in advance !!!!.

        Again thanks a lot for the previous help 😀 🙂 😀

        Reply

        • Hi Shivesh,

          Yes you could do this in VBA but why not just use Windows Explorer? Search for *.xlsx, then from the list that is produced, copy to where you want. Is this not a solution that will work for you?

          Regards

          Phil

          Reply

  37. Brilliant. Thank you – will look at your other stuff.
    I write minimal code but do good things with excel & data – this kind of stuff really expands what I do

    Reply

    • No worries Simon. Hope the other VBA articles can help you learn some new things. If you have any questions or issues just fire away.

      Phil

      Reply

    • Hi Jerald,

      I’m not sure what you mean. Do you mean how do you put a hyperlink link (e.g. to Sheet1) in the Ribbon?

      Regards

      Phil

      Reply

  39. I think I have a solution to the Ok/Cancel problem. See code below:

         'Phil's original code; Cancel or OK produce all files
     'Must set FileType to valid string so FSO doesn't error out
     'If FileType = vbNullString Then
     'FileType = "*.*"
     'End If

        'My alternative; Cancel cancels, Ok produces all files
    'StrPtr - Returns the address of the UNICODE string buffer.
            
     Select Case True
            
         Case StrPtr(FileType) = 0 'cancel.
         Exit Sub

         Case FileType = vbNullString 'no data input
         FileType = "*.*"
          
     End Select
    Reply

  40. The best explanation I’ve seen for recursion and getting file names/hyperlinks. Thank you!

    While it’s unlikely that someone wants to cancel after selecting the initial directory, if you change the InputBox function to Application.InputBox you can then trap the Cancel button event and do something (like exit the sub) if you wish:

         If FileType = False Then

          MsgBox "Process Cancelled"
          Exit Sub

     ElseIf FileType = vbNullString Then

          FileType = "*.*"

     End If
    Reply

    • Thanks Derek 🙂

      I like your solution. Don’t you find it odd that there are 2 different functions/ways to do this?

      I’ll change FileType to be a variant and update the code with your snippet.

      Thanks

      Phil

      Reply

  42. Brilliant post Philip, thank you! I can see lots of uses for this and snippets of code that can be built upon elsewhere as usual. Great explanation of recursive too, thanks, it really helps. Cheers!

    Reply

    • Thanks Dave!

      I’m glad it was clear and understandable especially the explanation on recursion. That can be tricky to get your head around.

      Cheers

      Phil

      Reply

      • Hi, all of the above has been brilliant so far! However, my files are deep within a network path and I believe the names are too long.

        Is there a solution to this? I can put the workbook in the actual folder I want to catalogue if that helps.

        Reply

        • Thanks Doug.

          Without knowing your environment and being able to test in it, I don’t know if you are hitting some limit a with the filesystem or my code.

          Putting the workbook in folder you want to catalogue won’t help as the entire path up to the root folder will still be listed. But, can you map a drive to the topmost folder in the structure (the one you are cataloging) ? That way the ‘root’ becomes that folder?

          Regards

          Phil

          Reply

          • Hi Philip, I could get a list of files in the folders but excel ad pdf files are not opening when clicked on the hyperlink generated.

            please help on this.

            Thanks
            Srikanth

          • Hi Srikanth,

            So other file types work, just not Excel and PDF?

            Are you getting an error when you click the links?

            If you edit the links that don’t work what do you see?

            Phil

Leave a Comment

Current ye@r *