So you have a load of PDF's, or scanned documents, or some nice pictures of your stamp collection*, and you want to get this list into Excel. But you also want to be able to click on that file name/link and have the document/image open up in Excel. Easy.
* I don't have a stamp collection, my photos would more likely be of my bikes, a Trek Speed Concept, and a Scott Foil
Since I wrote this I have written another macro that searches subfolders, and allows you to use wildcards so you can only list certain file types or files with specific names.
How This Code Works
In this code I make use of Application.FileDialog(msoFileDialogFolderPicker) to allow the user to choose the folder.
The Dir() function returns the files in the folder and I use this in a DO ... WHILE loop to list each file on the sheet.
If no files are found, then a message stating that is entered into the active cell, along with the path/name of the folder that was checked.
I've assigned the macro to a shape on Sheet 1 of this workbook, click the shape and the macro runs. But you can create your own shortcut for the macro, or a shape on another sheet, and/or save it in your Personal macro workbook
How to Use This Macro
- Click on a cell, this is where the list will start from.
- Click the big, green shape to run the macro and create the hyperlinked list.
- Make a cup of tea or a sandwich as you just saved yourself a load of time.
Note On the Generated LinksThe generated link (URL) has an absolute path i.e. the link includes the path to the file including the drive e.g. C:\Excel\Sales\Jan_Sales.xlsx. These links will only work on your computer, or where path is the same for all e.g. to a common network drive, or on another computer with the same folder structure as yours.
EnhancementsI can think of a couple of ways to make this macro more flexible and useful, such as;
- Create a userform to specify only certain file types be listed, or excluded. You could do the same thing using check boxes in the worksheet.
- Check sub-folders for the files.
The VBA Code
Enter your email address below to download the sample workbook.
Do you have a macro that just pulls the list of folders in a specified folder? Not trying to output the whole subfolder list. Just the first level of folders.
' Written by Philip Treacy, https://www.myonlinetraininghub.com/author/philipt
' My Online Training Hub https://www.myonlinetraininghub.com/create-hyperlinked-list-of-files-in-a-folder-using-vba
' Sep 2020
Dim MyDirectory As String
Dim MyFilename As String
Dim CurrentRow As Long
Dim StartingCell As String 'Cell where hyperlinked list starts
'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
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select folder to list files from"
'If a folder has been selected
If .SelectedItems.Count <> 0 Then
MyDirectory = .SelectedItems(1) & "\*."
'Get the first file, look for Normal, Read Only, System and Hidden files
MyFilename = Dir(MyDirectory, vbDirectory)
'Need to blank all existing hyperlinks
'Clear formatting from starting cell
'Enter default message in case no files are in folder
.Value = "No files found in " & MyDirectory
'While there are files, list them
Do While MyFilename <> ""
ActiveCell.Offset(CurrentRow).Hyperlinks.Add Anchor:=ActiveCell.Offset(CurrentRow), Address:=MyDirectory & MyFilename, TextToDisplay:=MyFilename
CurrentRow = CurrentRow + 1
'Get the next file
MyFilename = Dir
Indra Kurniawan Ramadhani
Dude thanks for the file
Thank you for the code.
Thanks for the use of this macro! This will save me much time when I need to send long lists of files for audits. Very cool!
Great code. I’m using the code to list files in difirent folders and when I move the folder, with the excel file inside the hyperlinks are not updated, meaning that the path of the files don’t change, which didn’t happened when using either Mozilla ou the ctrl+k shortcut, to create the hyperlinks. Do you have any ideas on how to solve it?
You need to remove the path/directory to the files so that they are relative to the workbook you are running the macro from. So when you move the files, the links still point to the files as long as the path to the files from the workbook is the same.
Here’s modified code that does this Create.Hyperlinked.List.of.Files.In.Folder.With.Relative.Paths.xlsm
Big time saver. We have invoices named 01-xxx, 02-xxx, 03-xxx etc. I am trying to create a master list of the pdf invoice files. I have gotten this to work, but when 01-xxx+1 is added, the process slows down as it has to re-write all of the subsequent links since it is based on the names. While this is not a big deal with 500 invoices, it is with 5000+. Is there a way to get the code to create the hyperlinks based on the date of the file with the oldest first?
You can create the hyperlink based on the file’s creation or last modified date – see Finding File Meta Data – but that would still require checking each file so probably wouldn’t be any faster than the current solution?
Do you want the code to check the folder only for files created/modified since a specified date, and create hyperlinks for only those files?
Having the code only check for a specified date may work if it could be something like Today()-7 or some variable.
I am using a slightly different code that uses a specific folder in a dropbox account.
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim enviro As String
enviro = CStr(Environ(“USERPROFILE”))
‘strFolderpath = enviro & “\Dropbox (OWS Corporate)\All Invoices\”
‘Create an instance of the FileSystemObject
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘Get the folder object
Set objFolder = objFSO.GetFolder(enviro & “\Dropbox (OWS Corporate)\All Invoices\”)
i = 0
‘loops through each file in the directory
For Each objFile In objFolder.Files
Range(Cells(i + 1, 3), Cells(i + 1, 3)).Select
‘create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
i = i + 1
This looks for the specific user variable path to dropbox vs asking what folder to use.
You get pretty much the same results
I’d love to do this on a Mac with the latest version of Excel.
Am I dreaming?
Mac is different, Ron de Bruin has developed a way to do the same thing, h has also an add-in for excel 2016: Loop through Files in Folder on a Mac
Indranee R. V
I like your VBA Code, simple and easy. Could you find for me, if I rename or move the file in the folder after create the hyperlink directory how to make it change automatically in excel directory.
There’s no way the link in Excel can know that the file has been moved or renamed, without running the code again.
Thanks mate. very helpful. just wondering if you already made the enhancement where only certain files can be listed such as pdf, excel?
Yes, it’s linked to at the top of this article create hyperlinked list of files using wildcards.
The code works perfectly and is a great improvement from the last post on this subject, so thank you!
I would like to be able to alter the code to search through the specified folder using a list of pdf’s as the search criteria (maybe a named range from a sheet)
So my file list would look something like below
I’m assuming this is possible?
Any chance you could point me in the right direction?
Kind regards Rob
No worries 🙂
Yes you can do what you want, how would you want the results presented? If the file(s) in your range were found in a (sub)folder I could create a hyperlink to that file, and list the path to it?
Sorry I’ve just realised it’s taken more than a month to reply!!
Creating a hyperlink list to the found files would be perfect, would it be possible to include hyperlinks for each pdf?
Any help would be great, say I had the list of pdf’s to look for in column J is that enough info?
No worries 🙂
Yes I can create a hyperlink to the found files. I’ll try to write the code so that you can dynamically specify where the list of files to be found are.
Give me a couple of days to work on it.
Great stuff Phil, thank you!
No worries Rob, glad to help.
I cannot get your script to work. I do not believe FileDialog is supported by a MAC. Is that true? If so, if there is a workaround? The problem is that although I use both a PC and a MAC, there are a lot of MAC only users out there.
I suggest trying your code a MAC just to see if it works and then letting your readers know ahead of time. If you know of a MAC solution, that wold be great.
Unfortunately yes, Mac doesn’t support FileDialog, and several other things that Windows does.
I do not have a Mac so I can’t test out any Mac specific code, but Ron de Bruin’s site has code which maybe able to get around your issue
I have only had a quick look through that as yet, so haven’t pinpointed the exact code you need, but as I said, I can’t test it myself anyway without a Mac.
I’ll have a better look though the code later, but if you find it before me, please let me know.
Thank you for the link to Ron site’s. I had found a snippet here for the FILEDIALOG MAC equivalent
However it does not solve the user community problem. Seems like this FILEDIALOG routing is such a basic function that MSFT would include it in an overall standard package. There is an opportunity here for you two since you are both experts.
Write FILEDIALOG, and some other common functions, for the mac using mac constructs. I bet there would be huge market for this. Bad news – you would need a MAC using VM software. Good news – you can have a PC and OS X windows open at the same time and can cut and past between them. Should make development easy!?
Glad you found Ron’s site useful.
Looks like he is actively developing for the Mac but I am not. Unfortunately I don’t know enough abut the Mac environment to start down that path.
Hi, this is fantastic, very interested in the listing sub-folders also, do you have a version that searches sub-folders? I am also inteested in the version aaron suggested below with searching specific types of files
Yes, this post has code for searching in subfolders and allows you to use wildcards to search only for specific file types
This is awesome, it will definitely help me in couple of things that I wanted to tidy up. Thanks a lot for sharing.
🙂 Glad you found it useful.
This code is fabulous … thank you so much! Opened up an entire area of coding I wasn’t familiar with.
That’s great to hear 🙂 Hopefully we can help you learn more VBA too
This is a very clever. Thank you for sharing.
You’re welcome Sanjiv
okay now …
Glad it’s sorted out. If you continue to have issues let me know.
thank you for your reply … I may have overused my computer yesterday! 🙂
my explorer has gone into some kind of limbo looking for the link to the workbook … not good.
You’re welcome Sajjad
Briljant script, i will start using this for reporting purposes.
I like this but work with so many sub-folders to keep myself organized. I hope you publish an enhanced version that will break out all subfolders with links to their contents.
I’ll modify the code so that it checks subfolders for you.
I’ve been looking for just this sort of thing. Perfect timing! How would one modify this to display only certain file types along with the date/time stamp on the files as well?
I’ll rewrite and use the FileSystemObject to retrieve the last modified date for each file.
Excellent Phil! I eagerly await the update 🙂