Removing folder and subfolder from hyperlink path

[NYG Irving]

This will do:


Sub M_snb()
With Application.FileDialog(4)
If .Show Then sn = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & .SelectedItems(1) & "\*.*"" /a-d /b /s").stdout.readall, vbCrLf)
End With

For Each it In sn
ActiveSheet.Hyperlinks.Add Cells(Rows.Count, 1).End(xlUp).Offset(1), it
Next
End Sub


Thank you for sharing this code. It is great. Is it possible to modify it so that in the text which is displayed for each hyperlink it is just the name of the document without the folder and subfolder of the filepath address? Thank you for any assistance you can offer.

[georgiboy]

Maybe the below:

Sub M_snb()
Dim sFiles As Variant, sFile As Variant, sVar As Variant

With Application.FileDialog(4)
If .Show Then
sFiles = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & .SelectedItems(1) & "\*.*"" /a-d /b /s").stdout.readall, vbCrLf)
End If
End With

For Each sFile In sFiles
If sFile <> "" Then
sVar = Split(sFile, "\")
ActiveSheet.Hyperlinks.Add Cells(Rows.Count, 1).End(xlUp).Offset(1), sFile, , , sVar(UBound(sVar))
End If
Next
End Sub