test.vba

Sub ChooseFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    'get the number of the button chosen
    Dim FileChosen As Integer
    FileChosen = fd.Show
    
    If FileChosen <> -1 Then
        'didn't choose anything (clicked on CANCEL)
        MsgBox "You chose cancel"
    Else
        'display name and path of file chosen
        Dim SelectedFile As String
        SelectedFile = fd.SelectedItems(1)
    End If
    Call WritePath(SelectedFile)
End Sub
Sub WritePath(file As String)
'
' Macro2 Macro
'
'
    With Sheets("Sheet1")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End With
    Cells(LastRow, 1).Formula = "='" + file & "'!" & Cells(2, 7).Address

End Sub