Results 1 to 2 of 2

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

  1. #1

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.)


    Click image for larger version. 

Name:	folder.jpg 
Views:	26 
Size:	62.8 KB 
ID:	2169

    Click image for larger version. 

Name:	experiment.jpg 
Views:	20 
Size:	86.5 KB 
ID:	2170

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    British Columbia
    Excel Version
    Excel 2010
    Hello amrinderminhas

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

    Sub Assign_Hyperlinks()
        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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts