My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

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
by

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.
Create Hyperlinked List of Files in a Folder Using VBA

More Hyperlink Posts

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
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
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
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
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
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
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
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
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
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
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
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
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
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Tag:

Reader Interactions

Comments

  1. Kyle

    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

      Hi Kyle,

      Try this

      Option Explicit

      Sub ListHyperlinkFiles()

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

  4. David Palmer

    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

  6. Victor Jakitsch

    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

  7. Phillip Hunt

    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

      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

        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

  9. Indranee R. V

    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

      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

    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

  11. Rob

    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

      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

        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

  12. John

    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

      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

      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

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

        Reply

        • Philip Treacy

          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

    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

  14. Nehal Pande

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

    Reply

  21. Rosie Clyde

    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

  22. Aaron

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *