Capture File Path Name and Location on Cell

[HotNumbers]

I need help on capturing the file Path and making it into a link to the source.

I have a Workbook were I import a sheet from an external workbook using VBA. I would like to be able to capture the path of the source and enter the file path into another cell with a link to the source. Hope I was clear, if not be happy to supply more info.

Here is the code i am using for importing:

Dim nName As Name
For Each nName In Names
If InStr(1, nName.RefersTo, "#REF!") > 0 Then
nName.Delete
End If
Next nName

On Error GoTo Error_Loader

Load:
NAME1 = Application.GetOpenFilename
Set NAME2 = ThisWorkbook

If NAME1 = False Then
ans = MsgBox("The load process has been canceled. " & vbNewLine & vbNewLine & _
"Click RETRY to restart the load process " & vbNewLine & _
"Click CANCEL to end the load process", vbRetryCancel, _
"Data")
If ans = vbRetry Then GoTo Load
If ans = vbCancel Then Exit Sub
End If

'''Data Report

Workbooks.Open Filename:=NAME1
Set Name3 = ActiveWorkbook
NM = ActiveWorkbook.Name

 

[abhi1687]

below line would add the hyperlink to source file:

ActiveSheet.Hyperlinks.Add ActiveSheet.Range("B2"), NAME1, , , "Source File"

change the range as per your need.