VBA- open up file path explorer and add hyperlink

[hissonrr]

Good day all,

Is it possible to use vba to open up the file path explorer (or a specific file path location), let you select a certain file, then paste that file path into a specific cell.

For example I would click on a button, the file path explorer would pop up and I could search for the specific file I need, select that file, then have it copy/paste the hyperlink into a specific cell?

Any help would be appreciated as i have never really done anything with hyperlinks before.

Thanks,

-R

 

[Yongle]

Try this for files on local drive
- cell can be selected with mouse or entered as a value

put this behind your button

    Call CreateHyperLink

put in standard module

Sub CreateHyperLink()
    Dim fd As Object, fPath As String, fName As String, cel As Range
    Set cel = Application.InputBox("Select a cell", "Add Link to File", , , , , , 8)
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = Environ("USERPROFILE") & "\"     'default folder path - MUST  end with[COLOR=#ff0000] [B]\[/B][/COLOR]
        .AllowMultiSelect = False
        .Title = "Please select a file."
        .Filters.Clear
        If .Show = True Then
            fPath = fd.SelectedItems(1)
            fName = Mid(fPath, InStrRev(fPath, "") + 1, 9999)
        Else
            Exit Sub
        End If
    End With
    cel.Hyperlinks.Add Anchor:=cel, Address:=fPath, TextToDisplay:=fName
End Sub

 

[hissonrr]

That is perfect,

Thanks

 

[Yongle]

thanks for feedback

 

[Esbi]

Hello.
Dear Yongle,
The hyperlink macro provided above works really well. I need the same macro but for a specific cell, without showing a cell in the input window.
Can you please provide a solution.
Thanks in advance,
Esbi.