Hyperlink macro

[Dave McLachlan]

hi,

I would like to build a macro that prompts the user for a file to be
inserted as a hyperlink.

That is, I would like it to open up a dialogue box with a list of files from
which the user can pick a file and then click ok. The macro should also
give the link a friendly name.

Thanks for any help.

 

[Ron de Bruin]

Hi Dave

This example use GetOpenFilename
A hyperlink to the file you select will be add in the active cell

give the link a friendly name.

What you want?

Sub Test()
Dim FName As Variant
Dim wb As Workbook
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
Address:=FName, TextToDisplay:=FName
End If
End Sub

 

[Dave McLachlan]

This is great. Thanks. How do I select a directory or path ?

 

[Ron de Bruin]

Hi Dave

Try this example sub
I will save your default folder first to set it back at the end

Sub Test2()
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
ChDrive "C"
ChDir "C:\Data"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
Address:=FName, TextToDisplay:=FName
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub