December 8, 2012

Hyperlinks the file to a cell in excel macro

Following  code that hyperlinks the file to a cell in excel when you type in the file name.

Private Sub Worksheet_Change(ByVal Target As Range)
'change "c:\tmp\" to whatever reference you need
'a cell, a public variable, a fixed string
If Target.Column = 16 Then
MakeHyperLink Target, "C:\Users\darlene.sippio\Documents\Temp"
End If
End Sub

Public Function MakeHyperLink(InRange As Range, _
ToFolder As String, _
Optional InSheet As Worksheet, _
Optional WithExt As String = "pdf") As String
Dim rng As Range
Dim Filename As String
Dim Ext As String

'Set InRange = Range("Q3").SpecialCells(xlCellTypeLastC ell)
'check to see if folder has trailing \
If Right(ToFolder, 1) <> "\" Then
Filename = ToFolder & "\"
Filename = ToFolder
End If
'check to see if need ext
If WithExt <> "" Then
'check to see if ext has leading dot
If Left(WithExt, 1) <> "." Then
WithExt = "." & WithExt
End If

End If
'if not explicit sheet then assign active
If InSheet Is Nothing Then
Set InSheet = ActiveSheet
End If
'now for every cell in range
For Each rng In InRange
'does range have value
If rng <> "" Then
'make hyperlink to file
InSheet.Hyperlinks.Add Anchor:=rng, Address:= _
Filename & rng.Text & WithExt, TextToDisplay:=rng.Text
End If


End Function

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.