How can multiple different hyperlinks be given to corresponding cells quickly?????

amrinderminhas

New member
Joined
Mar 25, 2014
Messages
12
Reaction score
0
Points
0
Hello everyone...
I have multiple number of 'excel' files at present in a folder as shown in figure 1 (in some situations the files can also be pdf or jpg) which i want to get hyperlinked to a new excel file as shown in Figure 2. Now i need that in Figure 2, in Column A (Where there is written Experiment 1, Experiment 2, Experiment 3 and so on...), each cell be hyperlinked with corresponding excel file quickly, instead of me selecting each cell and hyperlinking it with the excel file for all files.

Kindly make the arrangement such that the hyperlinked file can be of any format (i.e.. word, pdf, jpg, avi, mpeg etc.)

Figure1

folder.jpg


Figure2
experiment.jpg
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Hello amrinderminhas

If I understand what you are after and a VBA solution will suffice, you could try a macro like this

Code:
Sub Assign_Hyperlinks()
'http://www.excelguru.ca/forums/showthread.php?2837

    Dim rng As Range
    Dim cel As Range
    Dim LastRow As Long
    Dim Path As String
    Dim FileName As String
    
With Sheet1

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & LastRow)
    
    Path = "D:\New folder"   '<-- put your directory here

    If Right(Path, 1) <> Application.PathSeparator Then     'if path doesn't end in "\"
        Path = Path & Application.PathSeparator                  'add "\"
    End If
    
    For Each cel In rng
    
        FileName = Dir(Path & cel.Value & ".*", vbNormal)
                 
        If FileName <> "" Then cel.Hyperlinks.Add anchor:=cel, Address:=Path & FileName
            
    Next cel
    
End With

End Sub

Hope that helps
Good luck
NoS
 
Top