none
Excel: Add an hyperlink on a cell based on the value of an adjacent cell RRS feed

  • Question

  • I want to be able to have selected cells (column A) have an hyperlink to the path specified in the next column.

    I have a table with column A containing names of file.

    In column B i have the physical path to those files.

    I want to be able to add as hyperlink to the names in column A the path defined in column B

    How can this be done ?


    Thursday, March 6, 2014 6:27 PM

All replies

  • Assuming your data is in the following format:

    You could use the code below:

    Sub main()
    Dim i As Integer
    Dim flag As Boolean
    Dim strPath As String
    Dim strTextToDisplay As String
    flag = True
    i = 1
    While flag = True
        If Cells(i, 1) <> "" Then
            Range("A" + Strings.Trim(Str(1))).Select
            strPath = Cells(i, 2)
            strTextToDisplay = Cells(i, 1)
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=strPath, _
            TextToDisplay:=strTextToDisplay
            i = i + 1
        Else
            flag = False
           
        End If
    Wend
    End Sub

    Result:


    VBA Programming Service
    Thursday, March 6, 2014 8:29 PM
  • Re:  add a hyperlink

    Assuming file path does not include the file name or the last path separator and
    that the file name does include the file extension...

    (file name in A2 and truncated path in B2)

    =HYPERLINK(B2&"\"&A2,A2)
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 8:02 PM
    Thursday, March 6, 2014 8:36 PM