Auto link from excel to folder's files

Sub Example1()
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim I As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
    Next
End Sub

add below to ThisWorkbook module- excel file needs to be saved as Macro-Enabled Worksheet (.xlsm)

Private Sub Workbook_Open()
  Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xPath As String
    Dim I As Integer
'change file path on below line to correct Folder path
     xPath = "C:\Users\xxxx\Desktop\New folder"

    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
    Next
End Sub

yes it works, thank you so much,,

is there any way to catch the folder names too?

Yes but I don’t have access to my pc right now
Will do it tomorrow

thank you so much

Try this

Private Sub Workbook_Open()
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xPath As String
    Dim i As Long
      For Each Sheet In ThisWorkbook.Worksheets
        If Sheet.Name = "Files" Then
            Sheets("Files").Cells.Delete
            f = True
            Exit For
        Else
            f = False
        End If
    Next
    If Not f Then Sheets.Add.Name = "Files"
    Sheets("Files").Select
   
    xPath = "C:\Users\xxxx\Desktop\New folder"
 
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        i = i + 1
        ActiveSheet.Hyperlinks.Add Cells(i, 1), xFile.Path, , , xFile.Name
    Next
      
        With CreateObject("scripting.filesystemobject")
        For Each xFolder In .GetFolder(xPath).SubFolders
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 2, 1), _
                 Address:=xPath & Application.PathSeparator & xFolder.Name, _
                 TextToDisplay:=xFolder.Name
          i = i + 1
    Next

    End With
End Sub

thank you,, very good it works perfectly
is there any way to make any different sign like( font color, bold font ) for the folder names?

right now folder path is separated from the rest with empty row but yes you can have that too

Private Sub Workbook_Open()
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xPath As String
    Dim i As Long
      For Each Sheet In ThisWorkbook.Worksheets
        If Sheet.Name = "Files" Then
            Sheets("Files").Cells.Delete
            f = True
            Exit For
        Else
            f = False
        End If
    Next
    If Not f Then Sheets.Add.Name = "Files"
    Sheets("Files").Select
   
    xPath = "C:\Users\xxxx\Desktop\New folder"
 
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        i = i + 1
        ActiveSheet.Hyperlinks.Add Cells(i, 1), xFile.Path, , , xFile.Name
    Next
      
        With CreateObject("scripting.filesystemobject")
        For Each xFolder In .GetFolder(xPath).SubFolders
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 2, 1), _
                 Address:=xPath & Application.PathSeparator & xFolder.Name, _
                 TextToDisplay:=xFolder.Name
                 Cells(i + 2, 1).Font.Bold = True
                 Cells(i + 2, 1).Interior.ColorIndex = 8
          i = i + 1
    Next

    End With
End Sub

amazing as usual

Dear AC PORTA VIA
can add the "modified date" of the files in another column beside the column of the names of the files??
this will help to sort the file according to modified date.
kind regards,,

Dear AC PORTA VIA
IT WORKS , but for sorry it lists the modified date in another sheet, not in the same sheet

regards,,

It creates new sheet named “ Files” to prevent overwriting anything in active sheet
Everything you need should be on the same sheet-
Name DateLastModified DateCreated DateLastAccessed

thanks ,,
what i got is file names in a sheet and all other dates in another sheet, so does at what macro do?

Macro runs fine when I ran it
All info with file name hyperlink and modified date are on the same sheet named Files
There is something in your workbook that switch between sheet name Files and some other sheet in the middle of running macro
Do you have any other macro in your workbook?

perfect as usual, but there are extra "xPath" in the code, but when I remove it, it works fantastic
I really appreciated it

You are welcome
Glad it works for you