Hyperlink_Display full path file name instead of file name

[josros60]

Hi,

how can i modify the code below the title to display full path of file instead of filename?

Sub NCL_EFT_BACKUP()
Dim fd As FileDialog
Dim FilePicked As Integer
Dim UserRange As Range, UserCell As Range
'loop through selecteditems from FileDialog and create hyperlink offsetting cell by 1 column each time
Dim ary



Set fd = Application.FileDialog(msoFileDialogFilePicker)
' Change initial folder as needed or store it in a cell and reference that
fd.InitialFileName = "\\mtlnas01\EFT_backup\2021\"

fd.AllowMultiSelect = True
FilePicked = fd.Show

If FilePicked = 0 Then
    MsgBox "You hit cancel"
Else
    'Using inputbox to get selected Range
    Set UserRange = Application.InputBox(Prompt:="Please Select a Cell", Title:="Cell Select", Type:=8)
    'Get first cell of selected range
    Set UserCell = UserRange.Cells(1)
    
    'loop through selecteditems from FileDialog and create hyperlink offsetting cell by 1 column each time
    For i = 1 To fd.SelectedItems.Count
    ary = Split(fd.SelectedItems(i), "\")
      UserCell.Hyperlinks.Add Anchor:=UserCell, Address:=fd.SelectedItems(i), TextToDisplay:=ary(UBound(ary))
      Set UserCell = UserCell.Offset(0, 1)
    Next
End If
End Sub

thank you,

 

[Fluff]

Do you mean you want the hyperlink text to show the full path?

 

[josros60]

yes, please.

thank you,

 

[Fluff]

In that case try

      UserCell.Hyperlinks.Add Anchor:=UserCell, Address:=fd.SelectedItems(i), TextToDisplay:=fd.SelectedItems(i)

 

[josros60]

thank you, Worked. perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.