• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Create Hyperlinked List of Files in Subfolders

You are here: Home / Excel VBA / Create Hyperlinked List of Files in Subfolders
Create Hyperlinked List of Files in Subfolders
May 20, 2014 by Philip Treacy

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.
Please enter a valid email address.

Click here to download the macro enabled workbook

Create Hyperlinked List of Files in Subfolders

More Hyperlink Posts

Create Hyperlinked List of Files in a Folder Using VBA

Create Hyperlinked List of Files in a Folder Using VBA

Using Excel VBA, we can create a hyperlinked list of files from a folder in our worksheet
Excel Hyperlink Buttons

Excel Hyperlink Buttons

Easy navigation in your Excel workbooks using shapes and hyperlinks to switch between sheets. Create an Index page for all sheets in the workbook.
Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Save Time with Hyperlinks for Word, Excel and Outlook

Save Time with Hyperlinks for Word, Excel and Outlook

Use hyperlinks to make navigating in your documents easier. You can also link to external files on your hard drive to make them easier to find and open.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBATag: Hyperlink
Previous Post:interactive excel web app dashboardInteractive Excel Web App Dashboard
Next Post:Writing Excel Formulas EfficientlyWriting Excel Formulas Efficiently

Reader Interactions

Comments

  1. Zak Nazif

    September 20, 2022 at 1:43 pm

    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
    • Philip Treacy

      September 20, 2022 at 2:35 pm

      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
      • Zak Nazif

        September 20, 2022 at 5:49 pm

        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
        • Philip Treacy

          September 26, 2022 at 10:32 am

          Hi Zak,

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

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

          regards

          Phil

          Reply
      • Zakaria Nazif

        September 20, 2022 at 6:23 pm

        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
        • Philip Treacy

          September 26, 2022 at 10:34 am

          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
  2. will

    September 7, 2022 at 2:09 pm

    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
    • Mynda Treacy

      September 7, 2022 at 7:43 pm

      Hi Will,

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
      • will

        September 8, 2022 at 8:20 am

        Hi Mynda,
        Will do, it’s probably a much better idea too considering the vba involvement.
        I’ve found your tutorials and explanations super helpful and clear to understand. Thanks for all the hard work and great website! 🙂

        Reply
        • Mynda Treacy

          September 8, 2022 at 7:49 pm

          Wonderful to hear, Will!

          Reply
  3. Andy Guerra

    May 4, 2022 at 10:17 am

    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
    • Catalin Bombea

      May 5, 2022 at 4:57 pm

      You can write code to read the folders from SP using the SP API:
      https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-folders-and-files-with-rest

      Reply
  4. Raul

    February 16, 2022 at 8:33 pm

    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
    • Philip Treacy

      February 17, 2022 at 10:38 am

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

        February 21, 2022 at 9:16 pm

        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
        • Philip Treacy

          February 23, 2022 at 5:32 pm

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

            February 23, 2022 at 9:31 pm

            Works like a charm, thank you so much Phil!

            Keep up the good work! Your website is very useful ^^

          • Philip Treacy

            February 24, 2022 at 9:45 am

            No worries 🙂

  5. Darshan Rambhiya

    February 3, 2022 at 11:45 am

    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
    • Philip Treacy

      February 6, 2022 at 11:58 am

      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
  6. David

    June 4, 2021 at 12:05 am

    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
    • Philip Treacy

      June 7, 2021 at 9:00 am

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

        June 8, 2021 at 12:57 am

        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
        • Catalin Bombea

          June 8, 2021 at 2:08 am

          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
          • David Bacon

            June 8, 2021 at 2:12 am

            I have opened anew post with the code.

          • Catalin Bombea

            June 9, 2021 at 2:15 am

            Hi David,
            There cannot be more than 65530 links in a sheet, you have a workaround on forum.

  7. Junseuk

    March 26, 2021 at 4:04 pm

    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
    • Catalin Bombea

      March 28, 2021 at 2:47 am

      Hi Junseuk,
      Thanks for pointing that issue, we will fix the download.

      Reply
  8. John

    March 15, 2021 at 11:13 am

    Hi, I appreciate your SO GREAT tools!
    Unfortunately, I want to see the file list as your explanation.
    https://d13ot9o61jdzpp.cloudfront.net/images/hyperlinked-file-list.jpg

    However, when I download and run it, all folders and files are listed on one column.
    Could you please check and update the file.
    Create-Hyperlinked-List-of-Files-in-Subfolders.xlsm

    Reply
    • Catalin Bombea

      March 17, 2021 at 3:23 pm

      Hi John,
      What operating system are you using?

      Reply
      • John

        March 19, 2021 at 12:50 pm

        I’m using Windows 10 64bit and MS Office 2016.

        Reply
        • Catalin Bombea

          March 20, 2021 at 6:39 pm

          Hi John,
          You have to replace in code:
          Set DestinationRange = DestinationRange.Offset(OffsetRow) with :
          Set DestinationRange = DestinationRange.Offset(OffsetRow, 1)

          Reply
          • John

            March 22, 2021 at 3:24 pm

            I appreciate.

            Somehow it works however not correctly.

            It displays same sub-folder on the other column.

            https://d13ot9o61jdzpp.cloudfront.net/images/hyperlinked-file-list.jpg

            As above image, January and February folders are displayed on the same column.

            However when I revise script as your comment, same depth folders are displayed on the other column.

            Please check it again and please teach me.

          • Catalin Bombea

            March 23, 2021 at 5:48 pm

            Not following.
            Initially, everything was displayed in one column.
            After code changes, it displays as in your image? The image looks right.
            Please open a new topic on our forum and upload a sample file with your codes, will be easier to help you.

  9. LEI WANG

    February 8, 2021 at 1:17 pm

    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
    • Catalin Bombea

      February 11, 2021 at 12:37 am

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

        February 11, 2021 at 10:34 am

        Catalin, thank you for the information.

        Reply
  10. Austin

    July 29, 2020 at 3:41 am

    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
    • Philip Treacy

      July 29, 2020 at 9:24 am

      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
  11. Juan

    July 2, 2020 at 10:46 pm

    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
    • Catalin Bombea

      July 3, 2020 at 4:51 pm

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

        July 3, 2020 at 5:51 pm

        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
  12. SandeepKothari

    May 28, 2020 at 6:52 pm

    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
    • Philip Treacy

      May 28, 2020 at 10:32 pm

      Hi Sandeep,

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

      Regards

      Phil

      Reply
  13. burak

    November 27, 2018 at 9:19 pm

    You are awesome…

    Reply
    • Mynda Treacy

      November 27, 2018 at 9:21 pm

      Glad we could help, Burak 🙂

      Reply
  14. Robert Cline

    April 29, 2018 at 4:41 am

    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
    • Catalin Bombea

      April 29, 2018 at 2:12 pm

      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
  15. Anne Denniston

    April 13, 2018 at 10:46 pm

    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
    • Philip Treacy

      April 18, 2018 at 10:04 pm

      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
  16. Jonathan Agius

    April 9, 2018 at 4:50 pm

    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
    • Catalin Bombea

      April 10, 2018 at 3:12 pm

      Hi Jonathan,
      There is a tabular version of the code, see this comment.
      Here is the OneDrive link.
      Regards,
      Catalin

      Reply
      • Lei

        February 10, 2021 at 12:05 am

        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
        • Catalin Bombea

          February 10, 2021 at 5:19 am

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

            February 11, 2021 at 1:02 pm

            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”)”

          • Catalin Bombea

            February 11, 2021 at 3:12 pm

            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.

          • Lei

            February 12, 2021 at 4:03 am

            works well now. Thank you so much Catalin!

  17. Conrad Blume

    February 2, 2018 at 4:10 am

    ABSOLUTELY AMAZING!!!
    Thank you so much for sharing!

    Reply
    • Catalin Bombea

      February 6, 2018 at 2:33 pm

      We’re happy to help Conrad, thanks for feedback 🙂
      Regards,
      Catalin

      Reply
  18. stealth94rt

    January 23, 2018 at 11:52 pm

    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
    • Catalin Bombea

      January 24, 2018 at 12:57 am

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

        June 12, 2018 at 5:49 am

        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
        • Catalin Bombea

          June 12, 2018 at 1:20 pm

          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
          • Brandon Rausch

            October 9, 2018 at 5:14 am

            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?

          • Catalin Bombea

            October 10, 2018 at 9:26 pm

            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

          • Brandon Rausch

            October 11, 2018 at 1:01 am

            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?

          • Catalin Bombea

            October 11, 2018 at 11:53 am

            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

          • Brandon Rausch

            October 17, 2018 at 12:22 am

            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!

          • Catalin Bombea

            October 17, 2018 at 1:12 pm

            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

  19. James Higgins

    October 29, 2017 at 1:13 pm

    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
    • Catalin Bombea

      October 29, 2017 at 2:53 pm

      Hi James,
      A tabular version, close to what you want is this one: OneDrive Hyperlinks code
      Catalin

      Reply
  20. stealth94rt

    August 18, 2017 at 12:55 am

    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
    • Catalin Bombea

      August 18, 2017 at 8:11 pm

      Hi,
      You can try the solution from this article: excel-factor-18-dynamic-hyperlinks-and-the-magic, it should solve your problem.
      Catalin

      Reply
  21. Kakrishna

    August 12, 2017 at 6:35 pm

    This is what I have been looking for. Thanks

    Reply
    • Philip Treacy

      August 13, 2017 at 12:07 pm

      Glad it was useful.

      Reply
  22. NguyenDung

    August 30, 2016 at 12:15 am

    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
    • Catalin Bombea

      August 30, 2016 at 3:34 pm

      Hi,
      In comments section, you will find a code that is using the FileSystemObject, instead of Dir, it should work. Here is the comment with the code: create-hyperlinked-list-of-files-in-subfolders#comment-39146

      Reply
    • Catalin Bombea

      August 30, 2016 at 3:36 pm

      Hi,
      In comments section you will find a code that is using FileSystemObject instead of Dir, that should work. Here is the comment with the code: create-hyperlinked-list-of-files-in-subfolders#comment-39146
      Cheers,
      Catalin

      Reply
      • NguyenDung

        August 30, 2016 at 11:30 pm

        Thanks Catalin. But I get an error 13: ListFilesInSubFolders FSOFolder, LinksTable. I think it concern about variables, but I don’t know how to fix. Sorry I’m rather bad.
        My folder structure like that:
        é ____ ò ____ Folder1 ____ file.txt
        | |__ filé.txt
        |__ Folder2 ____ file.txt
        |__ filé.txt
        My folder structure like that:
        é (ò (Folder1(file.txt; filé.txt); (Folder2(file.txt; filé.txt)))
        Would you help me?

        Reply
        • Catalin Bombea

          August 31, 2016 at 12:00 am

          Hi ,
          Please upload sample files and your code on our Excel Forum, I’ll help you.
          Cheers,
          Catalin

          Reply
          • amhere

            August 31, 2016 at 2:47 am

            Thanks Catalin, my user is “amhere”. I have just post a topic.

  23. Karl Harvey

    June 6, 2016 at 4:01 am

    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
    • Catalin Bombea

      June 6, 2016 at 3:08 pm

      Hi Karl,
      You can try this version from our OneDrive folder. It will list the folder name in column A, and the file name in column B, in a defined table.
      Cheers,
      Catalin

      Reply
      • ZedLeb

        September 19, 2016 at 5:50 pm

        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
        • Philip Treacy

          September 19, 2016 at 8:42 pm

          You’re welcome, glad it was useful.

          Phil

          Reply
      • Karl Harvey

        October 5, 2022 at 2:16 pm

        Thanks – this has been very useful over the years!

        Reply
        • Catalin Bombea

          October 5, 2022 at 2:35 pm

          You’re welcome!

          Reply
  24. Jerome Kramer

    March 10, 2016 at 7:59 am

    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
    • Philip Treacy

      March 10, 2016 at 8:49 pm

      Hi Jerome,

      Great, glad it was helpful.

      Regards

      Phil

      Reply
  25. Dean

    March 9, 2016 at 7:06 pm

    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
    • Catalin Bombea

      March 10, 2016 at 8:32 pm

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

        March 11, 2016 at 8:43 am

        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
        • Catalin Bombea

          March 11, 2016 at 3:16 pm

          Great, glad to see you managed to make it work 🙂
          Cheers,
          Catalin

          Reply
  26. Dean

    March 8, 2016 at 7:56 am

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

      March 9, 2016 at 3:54 pm

      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
    • Philip Treacy

      March 15, 2016 at 9:58 am

      Hi Dean,

      No worries, here’s the link. This workbook lists the files down a single column.

      Regards

      Phil

      Reply
      • Brenda

        August 3, 2016 at 8:21 am

        Hi, Phil
        Love this, as I do with much of your stuff. How would I modify the code so that I can add the macro to my QAT without having to use your specific worksheet and its object?
        Thanks in advance,

        Reply
        • Philip Treacy

          August 5, 2016 at 8:47 am

          Thanks Brenda.

          This article explains how to add an icon to the QAT and this article explains how to move code into your PERSONAL.XLSB.

          Regards

          Phil

          Reply
  27. A. Zahab

    February 5, 2016 at 3:58 am

    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
    • Philip Treacy

      February 5, 2016 at 10:02 am

      You’re welcome. Glad you found it so useful 🙂

      Regards

      Phil

      Reply
  28. Brenda

    December 5, 2015 at 1:50 am

    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
    • Catalin Bombea

      December 5, 2015 at 7:04 pm

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

      December 7, 2015 at 12:46 pm

      Thank you. I will definitely try this again at work tomorrow.

      Reply
  29. Julian

    November 7, 2015 at 10:38 pm

    I love the way you explained “recursion” 。It’s quite wonderful.

    Reply
    • Philip Treacy

      November 8, 2015 at 10:22 am

      Thanks Julian 🙂

      Phil

      Reply
  30. Y. Ohno

    November 4, 2015 at 10:53 pm

    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
    • Catalin Bombea

      November 5, 2015 at 3:23 am

      You’re welcome 🙂

      Reply
  31. Y. Ohno

    November 4, 2015 at 7:52 pm

    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
    • Catalin Bombea

      November 4, 2015 at 8:37 pm

      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
  32. Y. Ohno

    November 3, 2015 at 5:27 pm

    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
    • Catalin Bombea

      November 4, 2015 at 5:55 pm

      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
  33. dražen

    June 9, 2015 at 4:06 pm

    thank you man 🙂

    Reply
    • Philip Treacy

      June 9, 2015 at 4:09 pm

      You’re welcome 🙂

      Phil

      Reply
  34. shivesh kumar

    March 24, 2015 at 8:58 pm

    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
    • Philip Treacy

      March 24, 2015 at 9:04 pm

      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
      • shivesh kumar

        March 25, 2015 at 5:16 pm

        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
        • Philip Treacy

          March 30, 2015 at 9:30 am

          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
  35. Simon

    September 3, 2014 at 8:05 pm

    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
    • Philip Treacy

      September 3, 2014 at 10:27 pm

      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
  36. JERALD

    July 11, 2014 at 4:43 pm

    madam, how will u use hyperlink in drawing object in menu. (ex: go to sheet 1….etc). just explain it’

    Reply
    • Philip Treacy

      July 11, 2014 at 7:53 pm

      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
  37. jomili

    May 22, 2014 at 1:41 am

    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
    • Philip Treacy

      May 22, 2014 at 12:35 pm

      Hi Jomili,

      Thanks for your code. Don’t you love these undocumented functions? Microsoft doesn’t officially support StrPtr either https://support.microsoft.com/kb/199824/en-us

      Good work, yes this works nicely

      Thanks

      Phil

      Reply
  38. Derek

    May 21, 2014 at 11:09 pm

    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
    • Philip Treacy

      May 22, 2014 at 12:45 pm

      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
  39. jef

    May 21, 2014 at 3:43 pm

    That worked nicely. Very useful code. Thanks for sharing.

    Reply
    • Philip Treacy

      May 21, 2014 at 4:14 pm

      Thanks Jef, glad it was useful.

      Phil

      Reply
  40. Dave

    May 20, 2014 at 8:07 pm

    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
    • Philip Treacy

      May 20, 2014 at 8:15 pm

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

        November 29, 2016 at 7:45 am

        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
        • Philip Treacy

          November 29, 2016 at 9:04 am

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

            October 28, 2020 at 11:55 pm

            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

          • Philip Treacy

            October 29, 2020 at 3:52 pm

            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 Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.