Automatically hyperlink to file without knowing extension

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.

Originally Posted by MarvinP

Hi,

I think this is a very easy problem if you use Power Query instead of VBA. Read:
https://www.thebiccountant.com/2017/...inks-to-excel/
http://efficiency365.com/2015/03/18/...iew-hyperlink/

Thanks for introducing me to Power Query. But it doesn't help as i want hyperlinks to local files in folder not to web urls. Beside i want hyper links to all the files which appear both in my naming list in column A and in folder. If there is a name in column A but there is no corresponding file in folder i want to skip it.

Thanks

.
I tried to insert the macro code here but the Forum servers keep refusing because of specific symbols contained in the code.

You'll have to review the attached workbook.

My apologies.

This macro will create a hyperlink for each file in the folder. It does not rely on your list in Col A.
Hopefully you can add to the macro to finish your project.

Thanks for your reply

I already tried using

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

and then merging it with this

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

but due to almost zero knowledge of coding i don't know how to do it.

Your solution will only create a list of hyperlinks which i have to manually copy and past in-front of each file. In some cases i have 20,000 names in my list whereas there are only 15,000 files. so if i use your solution i have to manually find those 5,000 missing name and apply the hyperlinks to rest.

This is exactly what i need if it gives me option of choosing folder.

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