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

    Figure1

    Click image for larger version. 

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


    Figure2
    Click image for larger version. 

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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

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
  •