I have the following code that hyperlinks the file to a cell in excel when you type in the file name. I need to alter this code and not sure how. I need for the code that when you type in file name it also finds the filename path and filename and hyperlink in the cell.
This is the code:
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 & "\"
Else
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
Next
End Function
For instance; if you have Y:\0000_CCB_MINUTS\2011\CCB_1135.pdf, if you type CCB_1135, the cell would hyperlink as "Y:\0000_CCB_MINUTS\2011\CCB_1135.pdf". So in a way it's searching the folder Y:\0000_CCB_MINUTS\ and it's subfolders, find the file name then hyperlink to that file within the subfolder.
Not sure if this could be done, but if that file is moved the hyperlink is redirected to that file. Is that possible also?
I'm working on it but need some help and clarification.
