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

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) & "\"
            
            'Get the first file, look for Normal, Read Only, System and Hidden files
            MyFilename = Dir(MyDirectory, 7)
        
            '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