Opening a document captured by Application.GetOpenFilename with hyperlink

[nilshalvarsson]

Hi,

Im am creating a user interface in Excel to keep track of a workflow
and I have two questions:

1. One of the steps in the workflow is to "upload" a document to
verify that the document has been created. I use a button (code below,
which I found on this forum) to paste the file name in a designated
cell, and this works fine. I would like the users to be able to click
the hyperlink to open the document. How is this done?

2. Is it possible to show just the file name, without the path? I do
not get the dir() command to work.

I've searched the forum, but I cannot find any answers to my
questions. Really thankful for any help!

//Nils

Private Sub CommandButton3_Click()

Dim FileName As Variant
Dim Title As String
Dim i As Integer
Dim Msg As String

' Set the dialog box caption
Title = "Select a File to Import"


' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)


' Exit if dialog box canceled
If Not IsArray(FileName) Then
MsgBox "No file was selected."
Exit Sub
End If

' Display full path and name of the files
For i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
Next i
MsgBox "You selected:" & vbCrLf & Msg
link1 = Msg

' Paste the file name as hyperlink in cell C13
Range("C13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Msg

End Sub

 

[Norman Jones]

Hi Nils,

Try something like:

'=============>>
Private Sub CommandButton1_Click()
Dim FName As Variant
Dim sStr As String
Dim iPos As Long

FName = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls), *.xls")

If FName = False Then
Exit Sub
Else
iPos = InStrRev(FName, Application.PathSeparator)
sStr = Mid(FName, iPos + 1)
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=FName, _
TextToDisplay:=sStr
End If
End Sub
'<<=============

 

[nilshalvarsson]

Hi Nils,

Try something like:

'=============>>
Private Sub CommandButton1_Click()
Dim FName As Variant
Dim sStr As String
Dim iPos As Long

FName = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls), *.xls")

If FName = False Then
Exit Sub
Else
iPos = InStrRev(FName, Application.PathSeparator)
sStr = Mid(FName, iPos + 1)
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=FName, _
TextToDisplay:=sStr
End If
End Sub
'<<=============

---
Regards,
Norman

















- Visa citerad text -

Norman,

It works perfectly! Thank you!!

//Nils