Create Hyperlinked List of Files in a Folder Using VBA

Philip Treacy

April 16, 2014

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.

Macro Enabled Excel Templates

Excel Chart Templates

51 thoughts on “Create Hyperlinked List of Files in a Folder Using VBA”

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

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

    • 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

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

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

    • 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

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

    • 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

      • 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

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

Current ye@r *