Excel macro to add an hyperlink that shows only the filename (no p

[Anna]

Hi all

I'm trying to make this work, just clicking a button, open a file dialog,
and adding an hyperlink in the selected cell showing only the name of the
file, without the path.
I have this code already done. The problem is: when calling the functions
LastIndexOf and Substring I receive a compiler error: Invalid qualifier for
any variable I use. How can I solve this? Thank you in advance!! ^^

Dim vrtSelectedItem As Variant
Dim Nom1 As String
Dim Nom2 As String
Dim y As String

Public Sub Linking_Click()

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

Nom2 = vrtSelectedItem
Nom1 = Ruta(Nom2)

If vrtSelectedItem <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=vrtSelectedItem
y = ActiveCell.Address
ActiveSheet.Range(y) = Nom1
End If
Next vrtSelectedItem

Else
End If
End With

Set fd = Nothing
End Sub

Function Ruta(item As String) As String

Dim lastLocation As Integer

lastLocation = item.LastIndexOf("\")
If lastLocation >= 0 Then
' remove the identified section, if it is a valid region
Ruta = item.Substring(0, lastLocation)
End If

End Function

 

[Joel]

Function Ruta(item As String) As String

Dim lastLocation As Integer

lastLocation = InStrRev(item, "\")
If lastLocation >= 0 Then
' remove the identified section, if it is a valid region
Ruta = Mid(item, lastLocation + 1)
End If

End Function