Get hyperlink address

[rjplante]

I would like to be able to open a dialog box, have the user select the file of interest, and then paste the file address into a particular cell.

The file would be located in the same folder the workbook is saved in, so I could use the something like thisworkbook.path to get the user to the desired folder. I just don't know the specific code to use.

Thanks for the help,

Robert


Here is what I have so far.

Sub PDF_SETUP()

'If the link cell is blank...
If Range("Z40").Value = "" Then
   
    Dim mypath As String
    Dim myfile As String

    mypath = ThisWorkbook.Path
    
    'Display dialog box to get file address.
    myfile = ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= (Display dialogue box here to select file)_
        "some file here" , TextToDisplay:= "nice name here.pdf"

     Range("Z40").value = myfile

'If the link cell is not blank...
ElseIf Range("Z40").Value <> "" Then

    'Open file with link in cell Z40
    Worksheets("Intro Page").Range("Z40").Hyperlinks(1).Follow

End If

End Sub