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.
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.
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.
Scroll down the list until you see Microsoft Scripting Runtime, then click the box beside this so it has a tick (check mark).
Click OK and you are done.
Well done, you survived 🙂
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 :
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
- 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.
- Next we check if D:\Sales has any subfolders, yes it does so we call ListFilesInSubFolders and pass it the folder D:\Sales\Excel
- 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
- 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
- Does D:\Sales\Excel have any more subfolders, yes. We call ListFilesInSubFolders and pass it D:\Sales\Excel\Feb
- List files in here. Does D:\Sales\Excel\Feb have any subfolders? No, so this code is finished.
- D:\Sales\Excel doesn't have any more subfolders. Stop running this copy of ListFilesInSubFolders and return to the code running in D:\Sales
- D:\Sales doesn't have any more subfolders, so the macro finishes.
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.
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.
When the macro starts it asks you to choose your 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.
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.
Option Explicit Public FSO As New FileSystemObject Private FileType As Variant Sub ListHyperlinkFilesInSubFolders() ' V2 ' Written by Philip Treacy, http://www.myonlinetraininghub.com/author/philipt ' My Online Training Hub http://www.myonlinetraininghub.com/Create-Hyperlinked-List-of-Files-in-Subfolders ' May 2014 Dim StartingCell As String 'Cell where hyperlinked list starts Dim FSOFolder As Folder Dim RootFolder As String Application.ScreenUpdating = False 'Make this a cell address to insert list at fixed cell 'e.g. StartingCell = "A1" StartingCell = ActiveCell.Address 'Ask for folder to list files from With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & "\" .Title = "Please select folder to list files from" .Show 'If a folder has been selected If .SelectedItems.Count <> 0 Then RootFolder = .SelectedItems(1) Set FSOFolder = FSO.GetFolder(RootFolder) 'Ask what type of files to look for FileType = InputBox("* and ? wildcards are valid " & vbCrLf & vbCrLf & " e.g. .xls* to list XLS, XLSX and XLSM" _ & vbCrLf & vbCrLf & "??st.* to list West.xlsx and East.xlsx" & vbCrLf & vbCrLf & "Just click OK to list all files.", _ "What type of files do you want to list?", "") If FileType = False Then 'Cancel pressed MsgBox "Process Cancelled" Exit Sub ElseIf FileType = vbNullString Then 'Nothing entered and OK pressed FileType = "*.*" End If 'Clear the active sheet to remove previous results ActiveSheet.Cells.Clear 'Enter default message in case no files are in folder With Range(StartingCell).Offset(1) .ClearFormats .Value = "No " & FileType & " files found in " & RootFolder .Select End With ' Call recursive sub to list files ListFilesInSubFolders FSOFolder, ActiveCell 'Autofit the columns containing our results Columns.AutoFit Else 'If no folder selected, admonish user for wasting CPU cycles 🙂 MsgBox "No folder selected.", vbExclamation End If End With Application.ScreenUpdating = True End Sub Sub ListFilesInSubFolders(StartingFolder As Scripting.Folder, DestinationRange As Range) ' Written by Philip Treacy, http://www.myonlinetraininghub.com/author/philipt ' My Online Training Hub http://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 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, 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