• 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
    • SALE 20% Off All Courses
    • 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
    • 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
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Create Hyperlinked List of Files in a Folder Using VBA

You are here: Home / Excel VBA / Create Hyperlinked List of Files in a Folder Using VBA
Create Hyperlinked List of Files in a Folder Using VBA
April 16, 2014 by Philip Treacy

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

Update

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.


Creating a list of hyperlinks to files in a folder using VBA

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.
Can you think of others?

The VBA Code

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

You can download the code in a .xlsm workbook.

Create Hyperlinked List of Files in a Folder Using VBA

More Hyperlink Posts

Create Hyperlinked List of Files in Subfolders

Create Hyperlinked List of Files in Subfolders

Using a little VBA we can create a hyperlinked list of files from subfolders right in our Excel 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:Macro Enabled Excel TemplatesMacro Enabled Excel Templates
Next Post:Excel Chart TemplatesExcel Chart Templates

Reader Interactions

Comments

  1. Kyle

    September 25, 2020 at 12:31 am

    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.

    Reply
    • Philip Treacy

      September 25, 2020 at 11:41 am

      Hi Kyle,

      Try this

      Option Explicit

      Sub ListHyperlinkFiles()

      ' Written by Philip Treacy, http://www.myonlinetraininghub.com/author/philipt
      ' My Online Training Hub http://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
      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) & "\*."
      Debug.Print MyDirectory

      'Get the first file, look for Normal, Read Only, System and Hidden files
      MyFilename = Dir(MyDirectory, vbDirectory)

      '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

      Reply
  2. Indra Kurniawan Ramadhani

    April 12, 2020 at 12:46 pm

    Dude thanks for the file

    Reply
    • Philip Treacy

      April 12, 2020 at 1:00 pm

      You’re welcome.

      Reply
  3. Dianna Justice

    January 21, 2019 at 7:03 pm

    Thank you for the code.

    Reply
    • Philip Treacy

      January 22, 2019 at 2:05 pm

      You’re welcome

      Reply
  4. David Palmer

    June 22, 2017 at 9:55 am

    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!

    Reply
    • Philip Treacy

      June 22, 2017 at 11:44 am

      You’re welcome.

      Reply
  5. Ximena Corbetto

    June 7, 2017 at 1:58 am

    Thank you!!

    Reply
  6. Victor Jakitsch

    April 21, 2017 at 12:55 am

    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?

    Reply
    • Philip Treacy

      April 21, 2017 at 10:43 am

      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

      Reply
  7. Phillip Hunt

    April 19, 2017 at 4:24 am

    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!

    Reply
    • Philip Treacy

      April 20, 2017 at 11:56 am

      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

      Reply
      • Phillip Hunt

        April 29, 2017 at 6:12 am

        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

        Reply
  8. Higashi West

    April 14, 2017 at 9:26 am

    I’d love to do this on a Mac with the latest version of Excel.

    Am I dreaming?

    Thanks.

    Reply
    • Catalin Bombea

      April 16, 2017 at 3:13 pm

      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

      Reply
  9. Indranee R. V

    March 29, 2017 at 11:41 am

    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.

    Reply
    • Philip Treacy

      March 29, 2017 at 12:54 pm

      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

      Reply
  10. Rey

    October 9, 2016 at 10:24 pm

    Thanks mate. very helpful. just wondering if you already made the enhancement where only certain files can be listed such as pdf, excel?

    Cheers!

    Reply
    • Philip Treacy

      October 10, 2016 at 7:54 am

      Hi Rey,

      Yes, it’s linked to at the top of this article create hyperlinked list of files using wildcards.

      Regards

      Phil

      Reply
  11. Rob

    September 5, 2014 at 11:07 pm

    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

    Reply
    • Philip Treacy

      September 7, 2014 at 7:42 pm

      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

      Reply
      • Rob

        October 14, 2014 at 12:39 am

        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

        Reply
        • Philip Treacy

          October 18, 2014 at 8:31 pm

          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

          Reply
          • Rob

            October 28, 2014 at 3:37 pm

            Great stuff Phil, thank you!

          • Philip Treacy

            October 28, 2014 at 6:28 pm

            No worries Rob, glad to help.

            Phil

  12. John

    August 22, 2014 at 11:22 pm

    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.

    Reply
    • Philip Treacy

      August 23, 2014 at 9:20 am

      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

      Reply
      • John

        August 23, 2014 at 9:07 pm

        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!?

        Reply
        • Philip Treacy

          August 25, 2014 at 9:24 am

          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

          Reply
  13. Natasha Keare

    May 22, 2014 at 1:23 pm

    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

    Reply
    • Philip Treacy

      May 22, 2014 at 1:25 pm

      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

      Reply
  14. Nehal Pande

    April 22, 2014 at 4:08 pm

    This is awesome, it will definitely help me in couple of things that I wanted to tidy up. Thanks a lot for sharing.

    Reply
    • Philip Treacy

      April 22, 2014 at 4:13 pm

      🙂 Glad you found it useful.

      Cheers

      Phil

      Reply
  15. Ginny Wagner

    April 17, 2014 at 11:07 pm

    This code is fabulous … thank you so much! Opened up an entire area of coding I wasn’t familiar with.

    Reply
    • Philip Treacy

      April 18, 2014 at 10:14 am

      That’s great to hear 🙂 Hopefully we can help you learn more VBA too

      Reply
  16. Sanjiv Daman

    April 17, 2014 at 7:05 am

    This is a very clever. Thank you for sharing.

    Reply
    • Philip Treacy

      April 17, 2014 at 10:06 am

      You’re welcome Sanjiv

      Reply
  17. Ginny Wagner

    April 17, 2014 at 1:29 am

    okay now …

    Reply
    • Philip Treacy

      April 17, 2014 at 10:07 am

      Glad it’s sorted out. If you continue to have issues let me know.

      Phil

      Reply
      • Ginny Wagner

        April 17, 2014 at 11:08 pm

        thank you for your reply … I may have overused my computer yesterday! 🙂

        Reply
  18. Ginny Wagner

    April 17, 2014 at 1:27 am

    my explorer has gone into some kind of limbo looking for the link to the workbook … not good.

    Reply
  19. Sajjad

    April 16, 2014 at 11:00 pm

    Tx Mynda

    Reply
    • Philip Treacy

      April 17, 2014 at 10:08 am

      You’re welcome Sajjad

      Regards

      Phil

      Reply
  20. Patrick Brom

    April 16, 2014 at 10:32 pm

    Briljant script, i will start using this for reporting purposes.

    Reply
    • Philip Treacy

      April 17, 2014 at 10:08 am

      Thanks Patrick.

      Phil

      Reply
  21. Rosie Clyde

    April 16, 2014 at 10:18 pm

    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.

    Reply
    • Philip Treacy

      April 17, 2014 at 3:04 pm

      Hi Rosie,

      I’ll modify the code so that it checks subfolders for you.

      Regards

      Phil

      Reply
  22. Aaron

    April 16, 2014 at 7:34 pm

    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?

    Reply
    • Philip Treacy

      April 17, 2014 at 3:03 pm

      Hi Aaron,

      I’ll rewrite and use the FileSystemObject to retrieve the last modified date for each file.

      Regards

      Phil

      Reply
      • Aaron

        April 17, 2014 at 7:05 pm

        Excellent Phil! I eagerly await the update 🙂

        Reply

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

Course Sale

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.

Subscribe to Our Newsletter

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

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

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
trustpilot excellent rating
 

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.