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 Links
The 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.Enhancements
I 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.
Please Share
If you found this useful or know someone that would, please use the buttons below to share this on your favorite social network
The VBA Code
You can download the code in a .xlsm workbook. You can also copy and paste the code below.Option Explicit
Sub ListHyperlinkFiles()
' Written by Philip Treacy
' My Online Training Hub https://www.myonlinetraininghub.com/create-hyperlinked-list-of-files-in-a-folder-using-vba
' April 2014
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
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
MyDirectory = .SelectedItems(1) & "\"
'Get the first file, look for Normal, Read Only, System and Hidden files
MyFilename = Dir(MyDirectory, 7)
'Need to blank all existing hyperlinks
Columns(Range(StartingCell).Column).ClearContents
'Clear formatting from starting cell
'Enter default message in case no files are in folder
With Range(StartingCell)
.ClearFormats
.Value = "No files found in " & MyDirectory
.Select
End With
'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
Loop
End If
End With
End Sub
David Palmer says
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!
Philip Treacy says
You’re welcome.
Ximena Corbetto says
Thank you!!
Victor Jakitsch says
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?
Philip Treacy says
Thanks Victor.
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
Regards
Phil
Phillip Hunt says
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?
Thank you!
Philip Treacy says
Hi Phillip,
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?
Phil
Phillip Hunt says
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.
Sub InvoiceLinks()
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
‘select cell
Range(Cells(i + 1, 3), Cells(i + 1, 3)).Select
‘create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
i = i + 1
Next objFile
End Sub
This looks for the specific user variable path to dropbox vs asking what folder to use.
You get pretty much the same results
Higashi West says
I’d love to do this on a Mac with the latest version of Excel.
Am I dreaming?
Thanks.
Catalin Bombea says
Hi,
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
Catalin
Indranee R. V says
Hai,
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.
Philip Treacy says
Thank you.
There’s no way the link in Excel can know that the file has been moved or renamed, without running the code again.
Regards
Phil
Rey says
Thanks mate. very helpful. just wondering if you already made the enhancement where only certain files can be listed such as pdf, excel?
Cheers!
Philip Treacy says
Hi Rey,
Yes, it’s linked to at the top of this article create hyperlinked list of files using wildcards.
Regards
Phil
Rob says
Phil,
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
54621.pdf
44555.pdf
78977.pdf
I’m assuming this is possible?
Any chance you could point me in the right direction?
Kind regards Rob
Philip Treacy says
Hi 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?
Regards
Phil
Rob says
Hi Phil,
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?
Kinds regards
Philip Treacy says
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.
Phil
Rob says
Great stuff Phil, thank you!
Philip Treacy says
No worries Rob, glad to help.
Phil
John says
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.
Philip Treacy says
Hi John,
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 http://www.rondebruin.nl/mac/section3.htm
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.
Regards
Phil
John says
Thank you for the link to Ron site’s. I had found a snippet here for the FILEDIALOG MAC equivalent
http://www.rondebruin.nl/mac/mac017.htm
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!?
Philip Treacy says
Hi John,
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.
Regards
Phil
Natasha Keare says
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
Philip Treacy says
Hi Natasha,
Yes, this post has code for searching in subfolders and allows you to use wildcards to search only for specific file types
https://www.myonlinetraininghub.com/create-hyperlinked-list-of-files-in-subfolders
Regards
Phil
Nehal Pande says
This is awesome, it will definitely help me in couple of things that I wanted to tidy up. Thanks a lot for sharing.
Philip Treacy says
🙂 Glad you found it useful.
Cheers
Phil
Ginny Wagner says
This code is fabulous … thank you so much! Opened up an entire area of coding I wasn’t familiar with.
Philip Treacy says
That’s great to hear 🙂 Hopefully we can help you learn more VBA too
Sanjiv Daman says
This is a very clever. Thank you for sharing.
Philip Treacy says
You’re welcome Sanjiv
Ginny Wagner says
okay now …
Philip Treacy says
Glad it’s sorted out. If you continue to have issues let me know.
Phil
Ginny Wagner says
thank you for your reply … I may have overused my computer yesterday! 🙂
Ginny Wagner says
my explorer has gone into some kind of limbo looking for the link to the workbook … not good.
Sajjad says
Tx Mynda
Philip Treacy says
You’re welcome Sajjad
Regards
Phil
Patrick Brom says
Briljant script, i will start using this for reporting purposes.
Philip Treacy says
Thanks Patrick.
Phil
Rosie Clyde says
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.
Philip Treacy says
Hi Rosie,
I’ll modify the code so that it checks subfolders for you.
Regards
Phil
Aaron says
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?
Philip Treacy says
Hi Aaron,
I’ll rewrite and use the FileSystemObject to retrieve the last modified date for each file.
Regards
Phil
Aaron says
Excellent Phil! I eagerly await the update 🙂