how to get file 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.

I have the macro started, I just don't know the correct language to get the file address. If someone could tell me the code to get that dialogue box to appear and select the file, that would be awesome.

Thanks for the help,

Robert

Here is what I have so far.

Sub PDF_SETUP()

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 _    
     "file name here", TextToDisplay:= "file name here.pdf"

    Range("Z40").vlaue = myfile

ElseIf Range("Z40").Value <> "" Then

    Worksheets("Intro Page").Range("Z40").Hyperlinks(1).Follow

End If

End Sub

 

[mikerickson]

I think you are looking for Application.GetOpenFileName

 

[niceguy_jay]

you can use this to capture the name of the file in the active cell

fn = Application.GetOpenFilename()
fn = Mid(fn, InStrRev(fn, "\") + 1)
ActiveCell.Value = fn

 

[rjplante]

Awesome!! I now get the file name I want placed into cell Z40, but I run into an error when I try to activate the hyperlink. The error message reads:

"Run-time error '9': Subscript out of range".

The code is below with the line flagged in red.

What do I do next?

    fn = Application.GetOpenFilename()
    fn = Mid(fn, InStrRev(fn, "\") + 1)
    
    Range("Z40").Value = fn

'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

 

[mikerickson]

You entered the filename into the Z40, not the whole path.
The whole path needs to be entered into the Z40's Hyperlink object.

 

[niceguy_jay]

You entered the filename into the Z40, not the whole path.
The whole path needs to be entered into the Z40's Hyperlink object.

Exactly...!!!
And for that the code needs to be modified in this way

fn = Application.GetOpenFilename()
Range("Z40").Value = fn

 

[rjplante]

Thanks. I have modified the code as listed below, and it does put the entire address in the window, but I still get the same error.

If Range("Z40").Value = "" Then

    fn = Application.GetOpenFilename()
    
    Range("Z40").Value = fn

ElseIf Range("Z40").Value <> "" Then

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

 

[niceguy_jay]

fn = Application.GetOpenFilename()
Range("Z40").Value = fn
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fn, TextToDisplay:=fn
Worksheets("Intro Page").Range("Z40").Hyperlinks(1).Follow

try using this...

 

[rjplante]

Thanks Niceguy, I found this with some digging

ActiveWorkbook.FollowHyperlink (Range("Z40").value)

It works great.

Thanks to all for the help!!

 

[mikerickson]

This worked for me

Sub test()
    Dim fPath As String, fName As String
    fPath = Application.GetOpenFilename
    fName = Split(fPath, Application.PathSeparator)(UBound(Split(fPath, Application.PathSeparator)))

    With Range("A1").Hyperlinks
        .Delete
        .Add anchor:=Range("A1"), Address:=fPath, TextToDisplay:=fName
    End With
    
    Range("A1").Hyperlinks(1).Follow NewWindow:=True
End Sub