List All Files in a Folder and Create Hyperlinks to Each File, Excel VBA

Previously in the article Find and List All Files and Folders in a Directory I’ve explained how you can list all the files in a folder using VBA for Excel. In this article I will explain how you can use VBA to list all the files in a folder, and create hyperlinks to each file.

This can be achieved using the code below:

Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("D:StuffBusinessTemp")
i = 1
'loops through each file in the directory
For Each objFile In objFolder.Files
    'select cell
    Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
    'create hyperlink in selected cell
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        objFile.Path, _
        TextToDisplay:=objFile.Name
    i = i + 1
Next objFile
End Sub

The code assumes the files are located in the path “D:StuffBusinessTemp”. The code below creates the hyperlink at the path specified. The text to display will be the name of the file:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    objFile.Path, _
    TextToDisplay:=objFile.Name

Assuming the following files are located in the directory:
File in Folder
The result will be the worksheet below:
Result
The cells in column A are hyperlinks to the files

You can download the file and code used in this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

16 thoughts on “List All Files in a Folder and Create Hyperlinks to Each File, Excel VBA”

  1. David says:

    This is really helpful, thanks!

    I am brand new to this stuff. Is there a way to select which sheet (e.g. “Sheet3”) holds the hyperlinks?

    Thanks!

    1. pedrumj says:

      Hi there

      No problem, glad I could help 🙂

      Yes, try the code below:


      Sub Example1()
      Dim objFSO As Object
      Dim objFolder As Object
      Dim objFile As Object
      Dim i As Integer

      'Create an instance of the FileSystemObject
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      'Get the folder object
      Set objFolder = objFSO.GetFolder("D:StuffBusinessTemp")
      i = 1
      Sheet3.Activate
      'loops through each file in the directory
      For Each objFile In objFolder.Files

          'select cell
          Sheet3.Range(Sheet3.Cells(i + 1, 1), Sheet3.Cells(i + 1, 1)).Select
          'create hyperlink in selected cell
          ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
              objFile.Path, _
              TextToDisplay:=objFile.Name
          i = i + 1
      Next objFile
      End Sub

      Feel free to contact me if you are still having trouble.

  2. Umair says:

    thank you very much for initializing this code, It seemed handy at first, but unfortunately i am not getting desired results. I thought that i Hyper linked file names will appear on their own once i am done putting the code in VBA, but they didn`t, Do i need to do something after that, I am trying to make a list of all files in folder “D:\Faxes”, FYI i replaced your code with this path. Will lover to hear from you,

  3. JANE says:

    Hi, I am trying to find a quick way to create folders and hyperlinks from an excel.

    I am new to the world of VBA but I have managed to find a VBA that creates the folders for the cells I highlight (eg I select the cells with 3, 4, 5 and it creates folders created 3, 4, 5)…..great saves loads of time!

    But I also need to hyperlink the cells to the created folders ….is there a way of doing this with VBA or another quick way?

    Hope someone can help.

    Thanks Jane

  4. JK says:

    Hi Pedrumj, I have been playing around trying to amend your code so that I can have Column A “File Name” (hyperlinked to file) and Collumn B “Modified date” not hyperlinked. Any ideas?

    Thanks
    JK

  5. AH says:

    How could i use this for only the newest saved file in the folder rather then all, or to do all but not to duplicate the ones that already exist?

    Thanks,
    AH

  6. vishwanath says:

    Thank you so much!
    Works well but does not list folders, only files are listed
    Any help would be appreciated

  7. Mohammed says:

    Hi

    Please tell me is this codes found here on the website free to use or can I please add a Citation …

  8. Ghulam Murtaza says:

    finally after 6 month of straggle i find this code
    yahooooooooooooooooooooooooo
    thnx thnxxxx thnxxxxx thnxxxxxx
    i am very very glad to see this code.
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great
    sir your are great

  9. George VASILE says:

    Great, it is work very well.
    Thanks
    GVE

  10. Eric says:

    I am trying to modify it so that it skip ones it already added instead of overwriting everything, and insert new items below the last row with data. Any advice on how to write that would be appreciated.

  11. yoav says:

    david Hi
    i used with the code and it didn’t work becouse the link is without the folder.
    9513020300#SHMP#LEAD FOOD CHEM#31.03.12.PDF

    it’s sould be look like:
    P:\Kosher_Purch\9510\9513020300#SHMP#LEAD FOOD CHEM#31.03.12.PDF

    way?

    thanks

  12. TJ says:

    Thanks so much for sharing this code. I had 100+ files I needed to add the hyperlinks to a database. Just made a slight change to remove the file extension:

    TextToDisplay:=Left(objFile.Name, InStr(objFile.Name, “.”) – 1)

    NOTE: This change will not work if the filename contains a period “.” before the file extension.

    Saved me allot of clicking back and forth to pull each hyperlink.

  13. amir says:

    This code is great.
    Im creating data sheet using this code for hyperlinks, but when i transfer it to another computer the path changes and obviously hyperlinks don’t work.
    is there a way to create “local path” one that doesn’t need the drive, but looks for folders and files only in its local folder?

  14. Chris says:

    Thanks for this, have to do this all the time. Your method is the most elegant I’ve found.

  15. PARDEEP SANGWAN says:

    Hi, I am trying to create hyperlink of folders with ref to the folder name mentioned in excel cell. Pl help me

Leave a Reply

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