VBA Add Hyperlink to Directory Path

[ievakr]

Hello,
What I'm trying to do is to find text, that matches the file name in a path and creates a hyperlink for it.
Now I have code, that this should be done only for the selected text, but still, it is not successful.
What I want to do e.g.: I have text Gantt.gif. It matches with the same file name in the Attachments folder and adds a hyperlink to it.



Sub HLink_Selected_Text()
Dim strPath As String
Dim oRng As Range
Dim sName As String
strPath = "..\attachments\"
Set oRng = Selection.Range
sName = Dir$(strPath & Trim(oRng.Text) & ".*")
If Not sName = "" Then
oRng.Hyperlinks.Add Anchor:=oRng, Address:=strPath & sName, TextToDisplay:=Trim(oRng.Text)
Else
Beep
MsgBox "Matching file not found"
End If
Set oRng = Nothing
End Sub

[gmayor]

Try the following instead. Much depends on where the attachments sub folder is located. The shortcut '..\' refers to the parent of the current folder. However there is no indication what the current folder is and that can be subject to change when working. It is therefore better to ensure that the folder is indeed where you expect it to be by inserting the full path as appropriate. In the example below the sub folder is the folder below the folder in which the template is stored. Change as appropriate.


Sub HLink_Selected_Text()
Dim strPath As String
Dim oRng As Range
Dim sName As String
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
strPath = ThisDocument.path & "\Attachments\"
Set oRng = Selection.Range
sName = Trim(oRng.Text)
If FSO.FileExists(strPath & sName) Then
oRng.Hyperlinks.Add _
Anchor:=oRng, _
Address:=strPath & sName, _
TextToDisplay:=sName
Else
MsgBox "The file" & vbCr & strPath & sName & vbCr & "was not found", vbCritical
End If
Set oRng = Nothing
Set FSO = Nothing
End Sub