Selecting a file in Windows Explorer

[Phrank]

Hi,

I have a userform that pops up, and the information entered by the
user gets entered into an Excel workbook. I would like to be able to
add an option (a button?) to the form that enables the user to open a
Windows Explorer window and select a file (on a network drive). I
would then like for a hyperlink to the selected file be copied to the
workbook. I've read several entries on the GetOpenFilename function,
but can't figure out how to work this into a hyperlink variable to be
added to the end workbook. I'd appreciate any help with this. Thanks.

Frank

 

[Guest]

Add a forms button and attach this macro code to it? Or if you're using the
control toolbox, then just cut the actual code from this and paste it into
the button's _Click event code. You could change ActiveCell to a specific
sheet/cell if you want.

Sub MakeHyperlinkFromFileOpen()
'select the cell you want the hyperlink
'placed into before calling this macro

Dim hyperlinkPath As String
Dim displayedText As String

hyperlinkPath = Application.GetOpenFilename
If hyperlinkPath = "False" Then
Exit Sub
End If
'if cell has text in it already, use that
'as the TextToDisplay value
If Not IsEmpty(ActiveCell) Then
displayedText = ActiveCell.Value
Else
displayedText = hyperlinkPath
End If
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:=hyperlinkPath, _
TextToDisplay:=displayedText

End Sub

 

[Phrank]

That worked great! Thank you very much!

Frank

Add a forms button and attach this macro code to it? Or if you're using the
control toolbox, then just cut the actual code from this and paste it into
the button's _Click event code. You could change ActiveCell to a specific
sheet/cell if you want.

Sub MakeHyperlinkFromFileOpen()
'select the cell you want the hyperlink
'placed into before calling this macro

Dim hyperlinkPath As String
Dim displayedText As String

hyperlinkPath = Application.GetOpenFilename
If hyperlinkPath = "False" Then
Exit Sub
End If
'if cell has text in it already, use that
'as the TextToDisplay value
If Not IsEmpty(ActiveCell) Then
displayedText = ActiveCell.Value
Else
displayedText = hyperlinkPath
End If
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:=hyperlinkPath, _
TextToDisplay:=displayedText

End Sub