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.