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.
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 🙂
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 :
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.
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.
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.
Please Share
The Code
As usual you can get the code in a .xlsm macro enabled workbook, or copy the code below.
Option Explicit
Public FSO As New FileSystemObject
Private FileType As Variant
Sub ListHyperlinkFilesInSubFolders()
' V2
' Written by Philip Treacy
' My Online Training Hub https://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
' 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
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
stealth94rt says
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!
Catalin Bombea says
Hi,
You can try the solution from this article: excel-factor-18-dynamic-hyperlinks-and-the-magic, it should solve your problem.
Catalin
Kakrishna says
This is what I have been looking for. Thanks
Philip Treacy says
Glad it was useful.
NguyenDung says
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.
Catalin Bombea says
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
Catalin Bombea says
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
NguyenDung says
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?
Catalin Bombea says
Hi ,
Please upload sample files and your code on our Excel Forum, I’ll help you.
Cheers,
Catalin
amhere says
Thanks Catalin, my user is “amhere”. I have just post a topic.
Karl Harvey says
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.
Catalin Bombea says
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
ZedLeb says
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.
Philip Treacy says
You’re welcome, glad it was useful.
Phil
Jerome Kramer says
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
Philip Treacy says
Hi Jerome,
Great, glad it was helpful.
Regards
Phil
Dean says
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
Catalin Bombea says
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
Dean says
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
Catalin Bombea says
Great, glad to see you managed to make it work 🙂
Cheers,
Catalin
Dean says
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
Dean says
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.
Philip Treacy says
Hi Dean,
No worries, here’s the link. This workbook lists the files down a single column.
Regards
Phil
Brenda says
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,
Philip Treacy says
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
A. Zahab says
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 🙂
Philip Treacy says
You’re welcome. Glad you found it so useful 🙂
Regards
Phil
Brenda says
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?
Catalin Bombea says
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
Brenda says
Thank you. I will definitely try this again at work tomorrow.
Julian says
I love the way you explained “recursion” 。It’s quite wonderful.
Philip Treacy says
Thanks Julian 🙂
Phil
Y. Ohno says
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
Catalin Bombea says
You’re welcome 🙂
Y. Ohno says
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
Catalin Bombea says
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
Y. Ohno says
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.
Catalin Bombea says
Hi Ohno,
If you use the File System Object, you can extract many file properties:
Set FSO = New Scripting.FileSystemObjectSet 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
dražen says
thank you man 🙂
Philip Treacy says
You’re welcome 🙂
Phil
shivesh kumar says
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
Philip Treacy says
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
shivesh kumar says
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 😀 🙂 😀
Philip Treacy says
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
Simon says
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
Philip Treacy says
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
JERALD says
madam, how will u use hyperlink in drawing object in menu. (ex: go to sheet 1….etc). just explain it’
Philip Treacy says
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
jomili says
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 SelectPhilip Treacy says
Hi Jomili,
Thanks for your code. Don’t you love these undocumented functions? Microsoft doesn’t officially support StrPtr either http://support.microsoft.com/kb/199824/en-us
Good work, yes this works nicely
Thanks
Phil
Derek says
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 IfPhilip Treacy says
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
jef says
That worked nicely. Very useful code. Thanks for sharing.
Philip Treacy says
Thanks Jef, glad it was useful.
Phil
Dave says
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!
Philip Treacy says
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
Doug says
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.
Philip Treacy says
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