Hi Guys,

I have thousands of file of various types (pdf,xlsx,docx,dat etc) in a specific folder. This directory keeps growing as well some time i delete some files from it. Every file in the directory has a unique no. I have listed the file names in spreadsheet say sheet 1 column A. I want to create hyperlinks to all those file in the same sheet i.e. sheet 1 column B. If there is no file present in the directory with same name (it has been delete), correspondent cell in column B should return NA or "File Not Available"

I found two solutions to this problem at the following link. http://www.excelforum.com/excel-prog...extension.html

@AlphaFrog suggested both of these solutions a couple of years ago.

The first was 
Sub File_List()
    
    Dim strPath As String, strFile As String, i As Long
    
    ' Prompt user to select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Temp\"               ' Default path
        .Title = "Please Select a Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub   ' User clicked cancel
        strPath = .SelectedItems.Item(1) & Application.PathSeparator
    End With
    
    Application.ScreenUpdating = False
    With Sheets.Add(After:=Sheets(Sheets.Count))
        strFile = Dir$(strPath & "*.*")
        Do While Len(strFile) > 0
            i = i + 1
            .Hyperlinks.Add Anchor:=.Range("A" & i), _
                            Address:=strPath & strFile, _
                            TextToDisplay:=strFile
            strFile = Dir$()
        Loop
        Columns.AutoFit
    End With
    Application.ScreenUpdating = True
    
End Sub
This prompts the user to choose folder where the files are residing and then creates hyperlinks in a new sheet.


The second code which looks more neat is as follows 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim strPath As String, strFile As String
    
    If Target.Count = 1 And Target(1).Column = 1 Then
    
        If Not IsEmpty(Target) Then
    
            strPath = ThisWorkbook.Path & Application.PathSeparator
            
            strFile = Dir$(strPath & Target & ".*")
        
            If Len(strFile) Then
                ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 1), _
                                           Address:=strPath & strFile, _
                                           TextToDisplay:=strFile
            Else
               Target.Offset(, 1).Value = "N/A"
            End If
            
        End If
        
    End If
    
End Sub
This does exactly what i want but there are two small issues first it doesn't give the user option to select folder where the files are residing as i want yo keep this spreadhsheet in a different and second it only shows hyperlink in any cell column b if i activate column correspondent cell in column A i.e. either type the value and press enter or press F2 and then enter. I have more than 20,000 cell i want to avoid pressing enter each time i copy a value in column A.


So i was wondering is there any way where the second macro code can be amended where it gives me option to select the folder (Just like code 1) i.e. from where to create hyperlinks and avoid the necessity to press enter to generate hyperlink.